Need Help wiht VBA code for personalized mass emails

[xlilsharky408x]

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

 

[Michael M]

Who is the email going to ???
Different recipients, same person ???
can you provide a small sample of the data ??

 

[xlilsharky408x]

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 Address	City	ST	Zip	County	property ID	email
1	1111 apple street	HONOAPIILANI	HI	99639	MAUI	1123456789	test1@gmail.com
2	2222 apple street	HONOAPIILANI	HI	36272	MAUI	1123456789	test2@gmail.com
3	3333 apple street	HONOAPIILANI	HI	36330	MAUI	1123456789	test3@gmail.com
4	4444 apple street	HONOAPIILANI	HI	36748	MAUI	1123456789	test4@gmail.com
5	5555 apple street	HONOAPIILANI	HI	71635	MAUI	1123456789	test5@gmail.com

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

 

[Michael M]

Try something like this on a TEST sheet

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]

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]

Which part are you referring to here

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

 

[xlilsharky408x]

Which part are you referring to here

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

Sorry, That isn't helping...
Do you mean you want the text "LOCK BOX CODE:" added to the string like this

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

 

[xlilsharky408x]

Yes!

 

[Michael M]

Have you tried experimenting with the Text strings...??

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