From excel rows to email

[Devvos]

Hi,

I am hoping someone could please help me.

My situation: I have to send information from excel via email, individually to different recipients.
The subject lines change every day but are constant for across all recipients. The body of the email is always individualized.


Example:

email: john@abc.com
Hi John,
You have 2 carrots today.
Thank you

email: Anne@efg.com
Hi Anne
You have 7 carrots today.
Thank you

Currently:
I use lookups to populate information such as the persons name in cell A1, the email body in B1, optional text in C1, email subject in D1 and the email address would be in E1.
There usually information in rows 1 to 20; sometimes more, sometimes less. I manually copy and past all of this information into different emails. I would like to automate this.

What I have found:
I found this code online at Mail a row to each person in a range but it is not exactly what I want.
It copies the whole row and pastes it in an email, as a table.

What I would like to see happen:

I would run the code and it will open a new messages on outlook for all the popualate rows.
It will fill the address, subject and body of the email with info from the populated rows - for each individual email address. There will be multiple rows.

Example

Email: Anne@efg.com
Subject:Your carrots today (D1)

In the body:
Hi Anne (A1)
Today you have 7 carrots. (B1)
Thank you (C1)

Email signature to be inserted from outlook (if possible)

Could you please help or point me to the right? Thank you so much for taking the time to look at this.

 

[severynm]

I have a pretty basic template that I use for VBA emails from excel. See a similar post here. You will need to customize that example slightly (for example, you don't need any reference to the dates so you can remove the if statement If dateRange.Rows(idx).Value ... ), but that should get you most of the way there.

To add your default signature to the email, add .Display as the first line in the With OutMail codeblock, as in this example.