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]

A bit quicker

Sub MM1()
Dim Source As Range, lr As Long, i As Long
With Application
    .ScreenUpdating = False
End With
    Set Mail_Object = CreateObject("Outlook.Application")
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
        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 Mail_Object = Nothing
With Application
    .ScreenUpdating = True
End With
End Sub

 

[xlilsharky408x]

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 & _

YES! But right before the "LOCK BOX CODE" i need the address

Hello
Blah blah part one
If you can please take a look at the following home for us at LOCK BOX CODE: 6789
Blah blah part two
Regards
your name
Your details

 

[Michael M]

Sub MM1()
Dim Source As Range, lr As Long, i As Long
With Application
    .ScreenUpdating = False
End With
Set Mail_Object = CreateObject("Outlook.Application")
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
        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 & _
            "Can you please take a look at the following home for us at " & Range("B" & i) & " " & Range("C" & i) & " " & Range("D" & i) & " " & Range("E" & i) & " " & Range("F" & i) & " with the 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 Mail_Object = Nothing
With Application
    .ScreenUpdating = True
End With
End Sub

 

[xlilsharky408x]

Sub MM1()
Dim Source As Range, lr As Long, i As Long
With Application
    .ScreenUpdating = False
End With
Set Mail_Object = CreateObject("Outlook.Application")
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
        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 & _
            "Can you please take a look at the following home for us at " & Range("B" & i) & " " & Range("C" & i) & " " & Range("D" & i) & " " & Range("E" & i) & " " & Range("F" & i) & " with the 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 Mail_Object = Nothing
With Application
    .ScreenUpdating = True
End With
End Sub

Thank you!!! I really appreciate it!! Saved me a lot of time. Any recommendations on post is should look at to get more familiar with VBA?

 

[Michael M]

Glad to help...
I'd probably remove the address reference in the body and use something like

.Body = "Hello" & vbCrLf & _
            "Blah blah blah blah blah blah blah blah blah blah blah blah " & vbCrLf & _
            "Can you please take a look at the home noted in the Subject line for us, with the LOCK BOX CODE: " & Right(Range("G" & i), 4) & vbCrLf & _

It would save mentioning it twice in the one email !!!

 

[xlilsharky408x]

A bit quicker

Sub MM1()
Dim Source As Range, lr As Long, i As Long
With Application
    .ScreenUpdating = False
End With
    Set Mail_Object = CreateObject("Outlook.Application")
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
        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 Mail_Object = Nothing
With Application
    .ScreenUpdating = True
End With
End Sub

I've been trying to figure out how to keep the default signature with outlook for weeks now with no luck. Do you know what lines I would have to edit in the code to send email with default signature?

 

[Michael M]

It's not a simple process...you will need to change your body text to HTML code, and a function is required to extract the signature from Microsoft folder
I don't have time to assist at the moment, but have a look here at example 2 !

Insert Outlook Signature in mail

Someone else may be able to jump in and modify for you, otherwise I might be able to take a look tomorrow !!