VBA Code - Email Distribution List from Excel I have written an VBA code that se

Question

VBA Code - Email Distribution List from Excel

I have written an VBA code that sends a daily email to a certain distribution list in Outlook.

Now, I would like to change the vba code to send the email based on a distribution list from the excel file.

We have different people updating the file daily and it is really difficult to ensure they are using the correct distribution list.

This is my vba code:

Dim olApp As Object 'Outlook.Application
     Dim olEmail As Object 'Outlook.MailItem
     Dim olInsp As Object 'Outlook.Inspector
     Dim olAttachments As Object 'Outlook.Attachments
     Dim wddoc As Object 'Word.Document
     Dim wdRng As Object 'Word.Range
    
     On Error Resume Next
     Set olApp = GetObject(, "outlook.application")
     If Err <> 0 Then Set olApp = CreateObject("outlook.application")
     On Error GoTo 0
     Set olEmail = olApp.CreateItem(0)
     Set olAttachments = olEmail.Attachments
        
     With olEmail
         .BodyFormat = 3
         Set olInsp = .GetInspector
         Set wddoc = olInsp.WordEditor
         .Display
        
         .To = "dailyreport"

         .Subject = "Daily Report - " & Format(Date, " dd.mm.yy")
                
                 
         olAttachments.Add "xxx" & Format(Date, " dd.mm.yy") & ".xls", _
            0, 1, "xxx"
        
         wddoc.Range.InsertBefore "Good morning!" & vbNewLine           
     End With

Explanation / Answer

76707
^Try something like:

Dim strAddressees As String
     Dim r As Range
    
     For Each r In Range("R1:R19") ''' range containing the distribution list
       If Len(strAddressees) = 0 Then
         strAddressees = r
      Else
         strAddressees = strAddressees & "; " & r
      End If
     Next

.To = strAddressees

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.