Personalize E-mail

maxx_daddy

Board Regular
Joined
Dec 3, 2010
Messages
74
Hi,
I have a working system that batches a generic e-mail, now the bossman wants it to send one e-mail to each person with their name on it.
I am "attempting" to add HTML to it too so I can use my signature.
Working off of Ron Debruins site, I have a column of e-mail addresses, with the persons first name either to the left or the right (doesnt matter) I need to have a single e-mail, created for each person. Right now I am hung up at
Dear "XXXX"
what am i doing wrong?



Sub SendEmail()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
For Each cell In Columns("L").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value Like "*@*" Then
Emailaddr1 = Emailaddr1 & ";" & cell.Value
End If
Next
With olMail
.To = Emailaddr1
.Subject = "Your recent quote"
.HTMLBody = "<H3><B>Dear " & cell.Offset(0, 0).Value & "</B></H3>" & "Please contact us to discuss bringing your account up to date.<BR><BR>" & "<B>Regards Ron de Bruin</B>"
.Display
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
cell.Offset(0,0) doesn't go anywhere.

If you want to go to the right of the e-mail address you'd use:

cell.Offset(,1). Conversely, if you wanted to go to the left you'd use cell.Offset(,-1)

Where Offset works like this: Offset(Row, Column).

HTH,
 
Upvote 0
somehow I am not referencing the cell correctly, becuase when i use this:
.HTMLBody = "<H3><B>Dear " & "joe" & "</B></H3>" & "Please contact us to discuss bringing your account up to date.<BR><BR>" & "<B>Regards Ron de Bruin</B>"

it will simply put "joe" in,

but when i use any sort of offset it faults out
"object variable or with block variable not set"
 
Upvote 0
See how this works:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SendEmail()<br>    <SPAN style="color:#00007F">Dim</SPAN> olApp <SPAN style="color:#00007F">As</SPAN> Outlook.Application<br>    <SPAN style="color:#00007F">Dim</SPAN> olMail <SPAN style="color:#00007F">As</SPAN> MailItem<br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Subj <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> EmailAddr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Recipient <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>        <SPAN style="color:#00007F">Set</SPAN> olApp = <SPAN style="color:#00007F">New</SPAN> Outlook.Application<br>        <SPAN style="color:#00007F">Set</SPAN> olMail = olApp.CreateItem(olMailItem)<br>    <br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Columns("L").Cells.SpecialCells(xlCellTypeVisible)<br>                <SPAN style="color:#00007F">If</SPAN> cell.Value <SPAN style="color:#00007F">Like</SPAN> "*@*" <SPAN style="color:#00007F">Then</SPAN><br>                    EmailAddr = cell.Value<br>                    <SPAN style="color:#00007F">With</SPAN> olMail<br>                        .To = EmailAddr<br>                        .Subject = "Your recent quote"<br>                        .HTMLBody = "Dear " & cell.Offset(, 1).Value & ", Please contact us to discuss bringing your account up to date."<br>                        .Display<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> cell<br>            <br>    <SPAN style="color:#00007F">Set</SPAN> olMail = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> olApp = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

With the example you have the reference to cell for the name is outside of the For Next construct that defined the range, so Excel can't find a range to link to that instance of cell.
 
Upvote 0
try this code;

table looks like this;
<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=227><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 width=64 align=right>0

</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl65 width=99>L

</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>M

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 align=right>1

</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl66>emails

</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>names

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right>2

</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>dfsc@dfgdf.com

</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>name1

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right>3

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>dfsc@dfgdf.com

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>name2

</TD></TR></TBODY></TABLE>​
 
Upvote 0
It is not allowing me to post html code.... :)

try this code;

table looks like this;
<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=227><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 width=64 align=right>
0
</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl65 width=99>
L
</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>
M
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 align=right>
1
</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl66>
emails
</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>
names
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right>
2
</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>
name1
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right>
3
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>
name2
</TD></TR></TBODY></TABLE>

HTML:
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Dim usern As String
For Each cell In Columns("L").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value Like "*@*" Then
EmailAddr = cell.Value
usern = cell.Offset(0, 1).Value
With olMail
.To = EmailAddr
.Subject = "Your recent Quote"
.HTMLBody = "<p>Dear " & usern & ",</p>" & _
"<p>Please do this....<br />" & _
"Please do this....<br />" & _
"</p>" & _
"<p>Thanks and regards<br />" & _
"Someone</p>"
.Display
End With
End If
Next

Set olMail = Nothing
Set olApp = Nothing
End Sub
 
Upvote 0
those are all great examples and they get the name in there, but they are only creating one e-mail for the last address on the list. I am hoping to get one e-mail per person.

PS you guys rock! (As always)
 
Upvote 0
it will send email to everyone in L column and address person with name in M column.
try
.Send
 
Upvote 0
Thanks again for all the guidance, this is faulting out. I created a clean sheet, set my references for outlook, and copied and pasted the code. It will send the first person an email and then defaults on the
" .To = EmailAddr"
thoughts?

Thanks,

Joe
 
Upvote 0
ok, trial and error are my friends

the SET commands need to be inside the loop for some reason. here is a working code for anybody that wants to have a basic follow up. Name and email address on a user form, you can set all of the HTML as needed and add signatures as well.

Sub SendEmail()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim usern As String
Dim mail As Range
Set mai1 = Range("mail")
For Each cell In Range("mail")
If cell.Value Like "*@*" Then
EmailAddr = cell.Value
usern = cell.Offset(0, -1).Value

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = EmailAddr
.Subject = "Your recent Quote"
.HTMLBody = "<p>Dear " & usern & ",</p>" & _
"<p>Please do this...." & _
"Please do this...." & _
"</p>" & _
"<p>Thanks and regards" & _
"Someone</p>"
.Send
End With
Set olMail = Nothing
Set olApp = Nothing
End If
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,147
Members
452,891
Latest member
JUSTOUTOFMYREACH

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
Back
Top