Macro - automatic email sending

Attached is my response.

I have a "standard" mailer program in VBA that sends mail. All you have to do is pass arguments to it. It takes the following arguments.
' ToString = List of addressees in To field, semicolon delimited
' SubjectString = Subject of the mail Message
' BodyString = Body of the mail message
' ReplyTo = (Optional) Reply to email address
' OnBehalfOf = (Optional) Mail on behalf of (must have permissions)
' CCString = (Optional) List of addressees in CC field, semicolon delimeted
' BCCString = (Optional) List of addresses in BCC filed, semicolon delimited
' AttachmentName = (Optional) List of full-path name attachments, semicolon delimited
' HTMLRange = (Optional) Range to include as HTML
' SendDisplay = (Optional) True = Send mail automatically, False = display only as draft - the default is FALSE

Just a word about how this works. With the arguments I supplied, the mail is sent out on behalf of the sender and the replies should go to the sender. However, the outgoing mail will wind up in the Outlook sent mail folder for the mail account actually sending the mail. Please note that in order to send the mail out on behalf of a person, that you must have permission to the mailbox. If you get an error then remove the argument in the OnBehaldOf, simply leave it blank between the commas.

Please Login or Register  to view this content.

An explanation on the mail flag: if it is set to false, the mailer displays the mail to be sent but does not send it. This will stack the mail up on the desktop. The display will give you a last chance to cancel the mail or to edit it before sending it. If the flag is set to true, the mail will go out automatically.

The program goes through the table on the Mail Table sheet and sets up the information on the Mail Information sheet. I try to get basic Excel to do as much of the work as possible, so I don't have to do it in code. It also makes things more flexible.

You do have the ability to send an attachment. Cell B8 on the Mail Information sheet is where to enter this information. Use a complete pathname to where the attachment exists. Leave this cell blank if there is no attachment.

The cells on the Mail Information sheet are either read from the Mail Table or are calculated.
- Today is simply the current date - do not modify this cell
- Due date is calculated. I set it for 15 days, you can set it to whatever you want.
- Recipient is read from the table - this information will be overwritten with the appropriated information as the program goes through the Mail Table.
- Recipient Email is read from the table - this information will be overwritten with the appropriated information as the program goes through the Mail Table.
- Sender Email is read from the table - this information will be overwritten with the appropriated information as the program goes through the Mail Table.
- Subject - fill this in with whatever information you want, even a formula
- Attachment - discussed above
- Mail Flag - discussed above
- Survey Type is read from the table - this information will be overwritten with the appropriated information as the program goes through the Mail Table.
- Body - I provided a formula to compute the body based on my understanding of what you need the program to do. You can replace this with whatever you want

As you can see, this program has the capability to be used for different kinds of mailings. Change the information in the Mail Table and play with the formulas on the Mail Information Sheet.