Using vba in Excel to create and send batch emails in Microsoft Outlook...

New Contributor



I've written some vba in Excel, to create and send a batch of emails (using Microsoft Outlook), based on info I have in an Excel Worksheet (e.g. email addresses, Salutations, etc).


The email I create using vba needs to have a file attached, and it needs to have some of the body text made bold. Once created the intention is to send the same email to many different email addresses, etc.


Currently I've not found a way, using vba, to attach a file to the template email, or make some of the email body text bold the way I would like.


Anyone ever done this before and could offer suggestions?


I've tried doing the same thing in outlook, using a pre-designed email template that has the file attached, and the necessary body text in bold, etc., but I'm unable to call this custom email template using vba. The only email that is created, via vba, is based on the standard empty template, etc.


And if I could call the custom template could I use vba to reference my Excel worksheet that contains the data I'm using?


Thank you,




3 Replies
A colleague is just doing something like this thru "Automate".

You need the message body to be formatted as html. So in your mailItem properties you need to set:

.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>some content that can use formatting tags</BODY></HTML>"


Alternatively, you can use utilities that cater to this use case: 
Here is a video demo of using Power Automate (mentioned by Stu in his answer above):
Here is a video demo using Visual CUT for this. It offers an html editor, simplifying the creation of a dynamic html message and inserting into it dynamic tokens from the excel data:
Disclosure: I'm the developer of Visual CUT.



Thanks for the info on using HTML formatting. I gave it a try and it worked except the text no longer has any paragraphs. I'll figure out how to put paragraphs back in, in HTML, and I think I'll be good to go.