Auto generate new mail with attachment

[TomWitsel]

Greetings,

Is there a way to...



We send a mail to each of our service people on the road with their time-table data made in excel. We do this each month, always the same file to the same person and this for 20 people.

Is there a way to automate the creation of the mail and/or the adding of the file?

Thank you!

[AlanCantor]

Maybe something like this?

Sub Email_Attachment_To_Group()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "person1@email.com;person2@email.com"     ' List of addresses to send
        .CC = ""
        .BCC = ""
        .Subject = "Monthly spreadsheet"
        .Body = "Attached is the monthly spreadsheet."
        .Attachments.Add ("C:\Users\123456\Documents\Monthly\test.txt") ' Specify full path to the file to attach
        .Display
    End With
         
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

[gmayor]

If you have 20 recipients, mail merge would make sense. Assuming each employee has a different variation of the worksheet then you could use E-Mail Merge Add-in to merge to e-mail with the filenames and recipients taken from the datasheet.