I wanted to export the contacts in Excel to an Outlook distribution list. I was able to do that with the given code. I have made the macro to run each time the Excel sheet is closed. This explicitly opens the Outlook and we have to select Save and Close each time. The following is my code:
Public Sub DistributionList()
Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objDistList As Outlook.DistListItem
Dim objMail As Outlook.MailItem
Dim objRecipients As Outlook.Recipients
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objDistList = objOutlook.CreateItem(olDistributionListItem)
Set objMail = objOutlook.CreateItem(olMailItem)
Set objRecipients = objMail.Recipients
objDistList.DLName = "Green"
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Dim lRow As Long
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Range("$A$1:$C" & lRow).AutoFilter Field:=3, Criteria1:="1"
Dim rRng As Range, cel As Range
Set rRng = ws.Range("B2:B" & lRow).SpecialCells(xlCellTypeVisible)
For Each cel In rRng
objRecipients.Add cel
Next
objDistList.AddMembers objRecipients
objDistList.Display
objRecipients.ResolveAll
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objDistList = Nothing
Set objMail = Nothing
Set objRecipients = Nothing
ws.AutoFilterMode = False
End Sub
I want to know if there are any way we can include code in the macro to avoid the Save and Close window opening.
Is there a way we can modify the code so that the distribution list opens in the background and saves itself?
