From excel rows to email

Devvos

New Member
Joined
Jun 9, 2009
Messages
14
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

severynm

Board Regular
Joined
Jan 8, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top