Macro to create a draft email from Excel

Hi all, so I want to create a macro that could draft an email, but not immediately sending it because I want to do a one last checking before sending.

So I created 3 sheets in the file I've attached:

1. Setup -> This is to setup the date and month that's written in the email, it's editable. Basically the Setup sheet is to cross-check if the day the email sent is not on either Saturday/Sunday. I've created a little button to check, so there's no issue there.
2. Heading -> This is to insert Subject and put the list of To and Cc the people to send the emails to
3. Email Content -> The content I want on the emails that I'd send

I'm confused to create a macro that could:
1. Input the Subject, To and Cc
2. Everything written in Sheet "Email Content" is what I want in the email. The contents in that sheet is changeable. In the sample email I have a 8-row table, but it can also be a 2-row table and others
3. Not immediately sending, just draft first so I can check if the contents are already good to go

Can anyone help me on this? I've attached the sample file that I've created with all the sheets.

Oh yeah, as a note, I use Ms. Outlook.

Thank you so much in advance!

I could not discern where you are getting the various pieces for the mail from, especially the mail addressees. However, you seem to be somewhat familiar with VBA. I am sending your workbook back to you with my mailer module included. What you need to do is write VB code to pass whatever you want to it.

Here is the list of parameters you can pass to it:

' 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 delimited
' 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 = True = Send mail automatically, False = display only as draft

You may only need the first three and the last. By default, the SendDisplay flag is set to false. This means, prepare the mail as a draft, but don't send it.

Also, you might have to send your table in the body as an HTML range.

Hi dflak and JEC, thanks for your replies!

dflak, I somewhat familiar with the If formula because I've used it for work too many times but still not getting the hang of the rest actually hahaha... So, for the sheets I've created, I want the macro to refer to those cells:

1. For the Sheet Heading -> the Subject in Outlook should refer to C1 and the emails sent To should refer to the list of email addresses in Row B4 onwards, while Cc should refer to Row C4 onwards
2. For the Sheet Email Content -> it's the body of the email... is it possible to make the body of the email to refer to all cells that is not blank, including the table in the form of the table, not attachment?

And I want the send command to be inside a button that I could click. I've attached the sample file with the button and table of To and Cc to make it clearer.

I'll be grateful for your reply! Thank you!

I took a couple of liberties in case you want to use this utility for other than sending out invoices.

The first thing I did was to put the information about the email in an Excel table. There are too many good reasons to use Excel Tables not to use them. One of them is that tables know how big they are, so as you add information to the table, charts, formulas and pivot tables adjust to accommodate the new data. Also, if you look at the VB code, you'll see how easy it is to refer to table parts by name making the code easier to write and to maintain.

I added two additional columns: Company and Salutation to make the mails a bit more personal. I can back this out.

On the Setup Page, I added a Mail_Prefix. You could change this to "Holiday Greetings for " or whatever other customization you would like.

Also in the email content sheet, I fill in the name of the contact in cell B3.

The body is a "fixed" range B3:H18. You may want to change this too if you are sending something other than an invoice. The named range to apply to what you want to send is Mail_Body. Use Formulas > Name Manager to change it.

Other than that, the code runs down the list of companies and gets the appropriate pieces to pass to the main mail program.

Separate each To and CC with a semi-colon just like if you were sending mail manually.

Hi, dflak, sorry to bother you again. So, I have a signature in my Outlook, but it doesn't show here when I generate the email with macro. How do I add my signature in the macro so that the generated email would be able to have signature in the bottom?

Thank you!

Hi dflak, sorry for updating this thread again.

What if I want to send two different emails with different To and Cc and different Mail Body? In the current macro, the Mail Body is only 1 and can be sent to many recipients, but what if I want to send 2 different emails entirely? So, for example... there will be "Email Content 1" and "Email Content 2" sheets. The "Email Content 1" is for the first row, and "Email Content 2" is for the second row for the "Heading" sheet.

Sorry for bothering you, thank you!

Off the top of my head ... make a copy of the spreadsheet and then create a second body on the second spreadsheet.

I need to look at the utility I sent you. At the core is a macro that takes a number of parameters. One of those parameters is the mail body. I have to see the code I wrapped around it calls this routine. Also, you might have some kind of a formula for the mail body that switches based on to whom the mail is sent. Once I look at the utility, I can make a better suggestion.

My apologies for being late on the replies. I've been on a whirlwind of interviews lately.

The email body is determined by a fixed named range: 'Email Content'!B3:H18.

Anything within this range of cells becomes the body of the email.

Things may work better if instead of being a name for a fixed range, the range is a named dynamic range. Generally speaking, this will change the number of rows and columns contained in the body. However, I'd need to know what possible bodies would look like.

What I need now is a way to determine which content to use. It appears that the content is dependent on who the mail goes to. I think I can come up with a way that you can have a sheet for every person in the table. You will define the sheet name and range for each person. I'll have to do some minor recoding to do this.

Here is a version that should work. The code tweak was easier than I expected.

There are two new columns in the table that defines who the mail goes to. One defines which sheet the body is found on, the other defines what cell range on that sheet you want to use. Of course, the same sheet / range combination can be used for multiple people. You do not need a unique combination for every record.