Excel to Outlook email automation (advanced version)
This VBA Excel macro is provided as a skeleton so you can send multiple emails from an Excel list. You have the option of either typing the email body in an Excel cell or to use a Word document to craft the email body.
If you choose the send text only email body then use the Type In The Excel cells method. This is handy for quick simple emails. Simply type the email body text in column G, the text can be different for each email or the same. If text entered in column G then this will override the Use Word Document for input method for that row only.
Using the Word Document to hold the email body is useful if your emails contain images, graphs, report and text formatting which would be otherwise impossible to achieve in an Excel cell. To use the Word document as the email body leave the cell in column G blank - if the cell contains text then this will override the Word document and the text in that cell will be used as the email body. Specify the path and name of the Word documents to be used for the emails body in column C. You can have individual Word documents for each email, or groups of emails or one document for all emails.
You also can tailor the Word documents email body by adding a specific first line to the top of the email. Enter the First Line text in column D and it will be imported into the email along side the Word document content. This might be useful if you have one Word Document set for all emails but you want to personalize the emails by adding a different greeting to each email. This technique is achieved by having a bookmark in the Word document that is replaced by your first line text. Be sure if you create your own Word document or edit the ones provided with this purchase to include this bookmark other wise the script will fail. Either keep the bookmark or edit the VBA code to exclude the section.
The VBA project is unlocked and free for you to view - edit the code to suit your needs. You may want to add more bookmark and custom text.
This macro was written and tested in Excel 2007 and should work with most versions of Office. The code is not bullet proof but makes a good foundation for further customisation. However it will work just fine if all the input parameters and filled correctly in the Excel list.
Open the Excel file and enable macros
On sheet 1 enter the email details;
- From email address in column A - Optional
- Subject in column B - Optional
- Email Body Word Document Sub Folder and Name in column C - Required if column G is blank
- First Line of Body – This will be the first line in the email body if the Word document is used to hold the email body. Only applicable if the Word document is used as the email body. This will allow you to change the first line of your email to suit each recipient - Optional
- To address in column E - Optional
- Cc address in column F - Optional
- Text only email body in column G, leave this blank if you want to use the Word document as an input for the email body – If blank you must enter a valid value in C
- Attachment Path in column H, this must be a valid path if used otherwise VBA will throw an unhandled error- Optional and It can be left blank if not required
- Mode in column I, choose from the dropdown menu: toDraft, Send or Display.
- Send will have the email sent without you seeing it on the screen. It can be found in your sent items.
- toDraft will save the email in the draft folder in Outlook so you can send it later.
- Display will generate and display the email, you can click send manually to send it later.
To run the macro click on the grey button to the top left of the columns.
The file you downloaded has in its VBA coding an option to Bcc the emails to a particular email address. In its original state this email is set to an example email address and the option is disabled. The user should edit the code if they want to set the Bcc to their own email address. To do this you should delete the Bcc lines of code or enter your email address to replace the test email address.
To report any bugs or error please contact http:www.frata.net. If you like any paid work done and customisation please write to us.
This program is provided as is with no warranty. No liability shall be taken for anything. By using this program you agree to these simple plain English conditions.
This product was added to our catalog on Tuesday 23 April, 2019.