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
 

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).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,282
Office Version
  1. 365
  2. 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
21,282
Office Version
  1. 365
  2. 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
21,282
Office Version
  1. 365
  2. 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
21,282
Office Version
  1. 365
  2. 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
21,282
Office Version
  1. 365
  2. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,277
Messages
5,836,354
Members
430,422
Latest member
sandyandy5

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