CODEWITHSUNDEEP
excelvbaoutlook
Pratyush Dash
Pratyush Dash

Reputation: 11

Need to send separate emails to multiple recipients from excel list

I have an excel list of people. I want to send emails to all as separate mails. Need to dynamically change the subject,body and recipients.

I have tried using vba and doing it. But i do not know how to dynamically change the subject and body. Also how can i enter multiple lines in the body?

i do not want all recipients to be sent the same mail.Image shows the field names in excel I need to have first name and first letter of last name in subject. And in body first line includes Hi firstname..And personal mail goes in the "to" field and professional as "cc"

Upvotes: 0

Views: 645

Answers (1)

Kürsat Aydinli
Kürsat Aydinli

Reputation: 121

Try it this way:

'Initialize objects
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)



With objOutlookMsg
  .To = ws.Range("A1") 'Assuming TO mail addresses are located here and separated with ";"
  .CC = ws.Range("B1") 'Assuming TO mail addresses are located here and separated with ";"
  .Subject = ws.Range("C1") 'Assuming subject is declared here
  .HTMLBody = ws.Range("D1") 'Assuming body is declared here
  If address_attachment_line.Value <> "" Then
    .Attachments.Add FilePath & FileName
  End If
  .Display
End With

Through storing the dynamic information in the ranges referenced by the code, you can control the single mails.

Furhermore to make linebreaks in the body text, just use the tag <br> as it is interpreted as HTML content.

Hope this helps you!

Upvotes: 1

Related Questions