Need Help wiht VBA code for personalized mass emails

xlilsharky408x

New Member
Joined
Jan 9, 2017
Messages
8
First post. I've been trying to figure the VBA code for the past 2 days and I just can’t seem to figure it out. From what I’ve been reading on here theirs a lot of knowledgeable people... Can anybody help? I have to send about 400 emails/property address.
1. They all have different/somewhat different subject lines but they all have “New Property:” before the address.
2. All emails are the same except the property address is added and so is the lockbox code in the middle of the email/paragraph
3. The last four digits of the property ID’s is the lockbox code. The formula I would use is =RIGHT(H7,4)

In column B : Street Address
In column C : City
In column D : ST
In column E : Zip
In column F : County
In column G : property ID (last four is used as lockbox code)



Sample email below.

Subject Line in outlook = New Property: [Insert property address here]

Body of email:
Hello,

Blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

If you can please take a look at the following home for us:
[Insert property address here]
Lockbox code: [Insert the last four digits of the property ID]

Blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Sincerely

Signature here from outlook
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Who is the email going to ???
Different recipients, same person ???
can you provide a small sample of the data ??
 
Last edited:

xlilsharky408x

New Member
Joined
Jan 9, 2017
Messages
8
Who is the email going to ???
Different recipients, same person ???
can you provide a small sample of the data ??

my bad I thought I uploaded that paet

They are goign to different recipients. Just have the email address in the column H


In column B : Street Address
In column C : City
In column D : ST
In column E : Zip
In column F : County
In column G : property ID (last four is used as lockbox code)
In column H : email address

<colgroup><col><col><col><col><col><col><col></colgroup><tbody></tbody>
Lot #Street AddressCitySTZipCountyproperty IDemail
11111 apple streetHONOAPIILANI HI99639MAUI1123456789 test1@gmail.com
22222 apple streetHONOAPIILANI HI36272MAUI1123456789test2@gmail.com
33333 apple streetHONOAPIILANI HI36330MAUI1123456789test3@gmail.com
44444 apple streetHONOAPIILANI HI36748MAUI1123456789test4@gmail.com
55555 apple streetHONOAPIILANI HI71635MAUI1123456789test5@gmail.com

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try something like this on a TEST sheet

Code:
Sub MM1()
Dim Source As Range, lr As Long, i As Long, OutApp As Object, OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "New Property: " & Range("B" & i) & " " & Range("C" & i) & " " & Range("D" & i) & " " & Range("E" & i) & " " & Range("F" & i)
            .to = Range("H" & i)
            .Body = "Hello" & vbCrLf & _
            "Blah blah blah blah blah blah blah blah blah blah blah blah " & vbCrLf & _
            "If you can please take a look at the following home for us at: " & Right(Range("G" & i), 4) & vbCrLf & _
            "Regards" & vbCrLf & _
            "your name" & vbCrLf & _
            "Your details"
            '.send ' will automatically send the email
              .display  'requires to send the Email manually
        End With
Next i
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
 

xlilsharky408x

New Member
Joined
Jan 9, 2017
Messages
8

ADVERTISEMENT

this is exactly what I was looking for! Thank you!!! i just need to tweak it a little and i should be good! How do i a space? Like to separate the paraphrases?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Which part are you referring to here

How do i a space? Like to separate the paraphrases?
 

xlilsharky408x

New Member
Joined
Jan 9, 2017
Messages
8

ADVERTISEMENT

Which part are you referring to here







Code:
.Subject = "New Property: " & Range("B" & i) & " " & Range("C" & i) & " " & Range("D" & i) & " " & Range("E" & i) & " " & Range("F" & i)            .to = Range("H" & i)            .Body = "Hello" & vbCrLf & _            "Blah blah part one " & vbCrLf & _            "If you can please take a look at the following home for us at: " & Right(Range("G" & i), 4) & vbCrLf & _

ON THIS PART THE ADDRESS HAS TO GO BEFORE THE 4 DIGIT CODE - IS THERE ANY WAY I CAN ADD "LOCK BOX CODE: " TO THE LINE? 
            "Blah blah part two " & vbCrLf & _
THIS PART             "Regards" & vbCrLf & _
THIS PART              "your name" & vbCrLf & _
            "Your details"
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Sorry, That isn't helping...
Do you mean you want the text "LOCK BOX CODE:" added to the string like this

Code:
"If you can please take a look at the following home for us at LOCK BOX CODE: " & Right(Range("G" & i), 4) & vbCrLf & _
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you tried experimenting with the Text strings...??
Code:
Sub MM1()
Dim Source As Range, lr As Long, i As Long, OutApp As Object, OutMail As Object
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "New Property: " & Range("B" & i) & " " & Range("C" & i) & " " & Range("D" & i) & " " & Range("E" & i) & " " & Range("F" & i)
            .To = Range("H" & i)
            .Body = "Hello" & vbCrLf & _
            "Blah blah blah blah blah blah blah blah blah blah blah blah " & vbCrLf & _
            "If you can please take a look at the following home for us at LOCK BOX CODE: " & Right(Range("G" & i), 4) & vbCrLf & _
            "Regards" & vbCrLf & _
            "your name" & vbCrLf & _
            "Your details"
            '.send ' will automatically send the email
              .display  'requires to send the Email manually
        End With
Next i
Set OutMail = Nothing
Set OutApp = Nothing
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,634
Members
414,082
Latest member
sasmita

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