Sending personalised emails from excel using VBA

**simon1985** · 11-26-2012, 10:08 PM

Hi There

I am looking for some help converting a VBS code to VBA (excel macro).

I created a VBS which has a few functions:

1. Create a HTML .msg file which contains a unique URL and image (downloaded from a website) based on the data entered into the first Input box. This email is then saved to a network location using the date from the Input as the file name (location of the file is irrelevant)

2. Create a secondary HTML email that uses the email address from one (additional) Input box, is addressed to the First name entered into another Input box. This email then has the first email that was created attached to it.

Code below:


result = InputBox("Enter FLEX Employee ID","FLEX Employee ID")
If IsEmpty(result) Then
Wscript.quit(1)

Else


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
  .Subject = "Stay two steps ahead with my clever 'myBroker' app for your iPhone"
  .htmlbody = "<img src='http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>"& "<html><body style='font-family:Calibri;font-size:15px;'>" & "Hi there, <br> <br>" & "Part of the first-rate service I offer as a mortgage broker is checking in regularly with my clients and key contacts. I do this to ensure you’re kept up to date with mortgage market movements to help keep you two steps ahead. <br> <br>" & "With more and more of my clients now using an iPhone, I’m really pleased to let you know I now have my own broker app that I would love you to download to your phone.<br> <br>" &  "The app has a host of tools and info at your fingertips. Staying in touch with me,finding out how much you could be saving on your loan, or if perhaps you’re thinking about a renovation or investment property the app can help give you an idea of how much you may be able to borrow. And if you know someone who may be needing help with their finance, you can share my app via email or a QR code. <br> <br>" & "You can check out more about how the app works here - www.mybrokerapp.com.au and you can download it by clicking on this " & "<a href=""http://itunes.apple.com/au/app/myafgbroker/id554555566?mt=8"">link here </a>. <br> <br>" &  "When you’ve downloaded the app onto your iPhone open it up and <b>scan the QR code below</b> or <b>when viewing this email on your iPhone</b> " & "<a href=""brokerapp://register/?id=" & result &""">click this link</a>, either way will give you instant access to my app! <br> <br>" & "<img src='http://qrcode.kaywa.com/img.php?s=8&d=" & result &"'> <br> <br>" & "Having a mortgage broker go into bat for you with your finance is the smart way to ensure you’re in the right finance solution and you’re two steps ahead. <br> <br>" & "Staying in touch has now got smarter too. <br> <br>" & "Kind regards, <br> <br>" & "PS – I’m not one to often email you with news like this, but please do let me know simply by replying to this email if you’d prefer not to receive similar messages from me in the future. </html>"
  .SaveAs "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"
End With




BrokerName = InputBox("Enter Broker First Name")
BrokerEmail = InputBox("Enter Broker eMail")
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
  .To = BrokerEmail
  .Subject = "Your own iPhone app is ready to download and share with your clients!"
  .htmlbody = "<img src='http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>" & "<html><body style='font-family:Calibri;font-size:15px;'>" & "Dear " & BrokerName & "," & "<br> <br>" & "<b>Your broker branded iphone app is ready for you and your clients to download. </b> <br>" & "As you know, you’ve been chosen as one of the first AFG brokers to receive your version of the app. Welcome to your very own tailored and branded iphone app that AFG has developed and built just for you.  It is designed to give your clients all the tools and functionality they need to keep on top of their finance, and to keep your details top of mind, all at the tip of their fingers from their iphone, leveraging the shift to mobile technology. <br> <br>" & "<b> How can I see what the app can do? </b> <br>" & "The best way to see how the app works and just what it can do is by clicking on this clever site we’ve created at www.mybrokerapp.com.au. <br>" & "Do scroll down on the site and you will find three short videos that illustrate the power of the app, how easy it is to use, and what it can do for your business. <br>" & "Don’t forget, the app works best with a photo of yourself and the active links to your social media accounts such as facebook.  So, do make sure we have this information from you so that you’re taking advantage of as many of its features as possible. <br> <br>" & "<b>What information do I need to know?</b> <br>" & "All the questions you may have in relation to the app should all be answered in the FAQ document attached. <br> <br>" & "<b>How do I download my app onto my own iPhone?</b> <br>" & "Once you’ve visited the site above and read the first attachment, please click on your customer email above, where you can scan your QR code or click on your link to allow you to download your app onto your own iPhone. <br>" & "This will ensure you get to know it before you invite your customers to download it. <br> <br>" & "<b>How do I share the app with people and encourage my clients to use it?</b> <br>" & "We’ve attached an email template for you to use to send to your clients.<br>" & "Please just save this email template onto your computer and then once you open it up, it will launch in Outlook and you’ll be able to send to as many of your email contacts as possible.<br>" & "Of course there are also built in share features within the app itself, which is another way of promoting the app across your client base.<br> <br>" & "<b>It’s a new world, and we’re here to help you with any questions you may have.</b> <br>" & "If you have any questions on how to install the app or how to use it, please don’t hesitate to contact us and we can walk you through how it all works. <br> <br>" & "Kind regards, <br> <br>" & "AFG’s new way of working team </html>"

  .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Information Sheet - my AFG Broker App.pdf"
  .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"

  .Display
End With

End If

This VBS works great for one by one creation - However now I have the need to send this custom email with the other custom email attached, too about 4,000 members.

I have an excel workbook that contains all 4,000 contacts, and all the data that I would use for creation:

A - Member ID
B - First Name
C - Last Name
D - Email
F - URL (URL is a concatenation of the URL & Member ID which is the link to the unique image)

What I need is a vba macro that will do everything my vbs does, but for each record in my work book.

Can anybody help?

Thank You
Simon

**TMS** · 11-26-2012, 11:36 PM

Please provide a sample workbook with some typical data.

Regards, TMS

**simon1985** · 11-26-2012, 11:44 PM

Thank you for your response.

Example.xls

Attached is an example of the workbook and how the data will appear.

Thank You
Simon

**TMS** · 11-27-2012, 10:28 AM

Hi Simon

This isn't quite right but the principle is demonstrated here. You can't have such long lines in VBA code so I have created a "body" string and then used that. However, the first saved email isn't structured properly for some reason so you only get the second half of it. That aside, it builds and saves the first email, builds the second email and attaches the first email ( I didn't have a second attachment) and displays each one for review, edit and send.

If/when you get the structure of the body of the first email sorted, I'd appreciate seeing it ... I'm not big on HTML so I don't really know where to start and it's probably not really worth me spending time on it if you can fix it quickly.

So, the code is going to look something like this:

Option Explicit

Sub TestSendHTMLEmail()
Dim lLR As Long
Dim i As Long
Dim olApp As Object
Dim olMsg As Object
Dim result
Dim d
Dim sBody As String
Dim BrokerName
Dim BrokerEmail

lLR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
    result = Range("A" & i).Value
    BrokerName = Range("B" & i).Value & " " & Range("C" & i).Value
    BrokerEmail = Range("D" & i).Value
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    
    sBody = _
        "<img src='http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>"
    sBody = sBody & "<html><body style='font-family:Calibri;font-size:15px;'>"
    sBody = sBody & "Hi there, <br> <br>"
    sBody = sBody & "Part of the first-rate service I offer as a mortgage broker is checking in regularly with my clients and key contacts. I do this to ensure you're kept up to date with mortgage market movements to help keep you two steps ahead. <br> <br>"
    sBody = sBody & "With more and more of my clients now using an iPhone, I'm really pleased to let you know I now have my own broker app that I would love you to download to your phone.<br> <br>"
    sBody = sBody & "The app has a host of tools and info at your fingertips. Staying in touch with me,finding out how much you could be saving on your loan, or if perhaps you're thinking about a renovation or investment property the app can help give you an idea of how much you may be able to borrow. And if you know someone who may be needing help with their finance, you can share my app via email or a QR code. <br> <br>"
    sBody = sBody & "You can check out more about how the app works here - www.mybrokerapp.com.au and you can download it by clicking on this "
    sBody = sBody & "<a href=""http://itunes.apple.com/au/app/myafgbroker/id554555566?mt=8"">link here </a>. <br> <br>"
    sBody = sBody & "When you've downloaded the app onto your iPhone open it up and <b>scan the QR code below</b> or <b>when viewing this email on your iPhone</b> "
    sBody = sBody & "<a href=""brokerapp://register/?id="
    sBody = sBody & result
    sBody = sBody & """>click this link</a>, either way will give you instant access to my app! <br> <br>"
    sBody = sBody & "<img src='http://qrcode.kaywa.com/img.php?s=8"
    sBody = sBody & d = " "
    sBody = sBody & result
    sBody = sBody & "'> <br> <br>"
    sBody = sBody & "Having a mortgage broker go into bat for you with your finance is the smart way to ensure you're in the right finance solution and you're two steps ahead. <br> <br>"
    sBody = sBody & "Staying in touch has now got smarter too. <br> <br>"
    sBody = sBody & "Kind regards, <br> <br>"
    sBody = sBody & "PS - I'm not one to often email you with news like this, but please do let me know simply by replying to this email if you'd prefer not to receive similar messages from me in the future. </html>"
    
    With olMsg
      .Subject = "Stay two steps ahead with my clever 'myBroker' app for your iPhone"
      .htmlbody = sBody
    '    .SaveAs "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"
        .SaveAs "c:\test\" & result & ".msg"
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    
    sBody = _
        "'http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>"
    sBody = sBody & "<html><body style='font-family:Calibri;font-size:15px;'>"
    sBody = sBody & "Dear "
    sBody = sBody & BrokerName
    sBody = sBody & ","
    sBody = sBody & "<br> <br>"
    sBody = sBody & "<b>Your broker branded iphone app is ready for you and your clients to download. </b> <br>"
    sBody = sBody & "As you know, you've been chosen as one of the first AFG brokers to receive your version of the app. Welcome to your very own tailored and branded iphone app that AFG has developed and built just for you.  It is designed to give your clients all the tools and functionality they need to keep on top of their finance, and to keep your details top of mind, all at the tip of their fingers from their iphone, leveraging the shift to mobile technology. <br> <br>"
    sBody = sBody & "<b> How can I see what the app can do? </b> <br>"
    sBody = sBody & "The best way to see how the app works and just what it can do is by clicking on this clever site we've created at www.mybrokerapp.com.au. <br>"
    sBody = sBody & "Do scroll down on the site and you will find three short videos that illustrate the power of the app, how easy it is to use, and what it can do for your business. <br>"
    sBody = sBody & "Don't forget, the app works best with a photo of yourself and the active links to your social media accounts such as facebook.  So, do make sure we have this information from you so that you're taking advantage of as many of its features as possible. <br> <br>"
    sBody = sBody & "<b>What information do I need to know?</b> <br>"
    sBody = sBody & "All the questions you may have in relation to the app should all be answered in the FAQ document attached. <br> <br>"
    sBody = sBody & "<b>How do I download my app onto my own iPhone?</b> <br>"
    sBody = sBody & "Once you've visited the site above and read the first attachment, please click on your customer email above, where you can scan your QR code or click on your link to allow you to download your app onto your own iPhone. <br>"
    sBody = sBody & "This will ensure you get to know it before you invite your customers to download it. <br> <br>"
    sBody = sBody & "<b>How do I share the app with people and encourage my clients to use it?</b> <br>"
    sBody = sBody & "We've attached an email template for you to use to send to your clients.<br>"
    sBody = sBody & "Please just save this email template onto your computer and then once you open it up, it will launch in Outlook and you'll be able to send to as many of your email contacts as possible.<br>"
    sBody = sBody & "Of course there are also built in share features within the app itself, which is another way of promoting the app across your client base.<br> <br>"
    sBody = sBody & "<b>It's a new world, and we're here to help you with any questions you may have.</b> <br>"
    sBody = sBody & "If you have any questions on how to install the app or how to use it, please don't hesitate to contact us and we can walk you through how it all works. <br> <br>"
    sBody = sBody & "Kind regards, <br> <br>"
    sBody = sBody & "AFG's new way of working team </html>"
    
    With olMsg
        .To = BrokerEmail
        .Subject = "Your own iPhone app is ready to download and share with your clients!"
        .htmlbody = sBody
        .Attachments.Add "c:\test\" & result & ".msg"
'       .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Information Sheet - my AFG Broker App.pdf"
'       .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"
        
        .Display
    End With
Next 'i
End Sub

Regards, TMS

**simon1985** · 11-27-2012, 08:33 PM

Hi Trevor

My sincerest thanks for this! It works a treat.

I have removed the sBodys and modified the .htmlbody


Option Explicit

Sub TestSendHTMLEmail()
Dim lLR As Long
Dim i As Long
Dim olApp As Object
Dim olMsg As Object
Dim result
Dim d
Dim sBody As String
Dim BrokerName
Dim BrokerEmail

lLR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
    result = Range("A" & i).Value
    BrokerName = Range("B" & i).Value & " " & Range("C" & i).Value
    BrokerEmail = Range("D" & i).Value
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
     
    With olMsg
      .Subject = "Stay two steps ahead with my clever 'myBroker' app for your iPhone"
      .HTMLBody = "<img src='http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>" & "<html><body style='font-family:Calibri;font-size:15px;'>" & "Hi there, <br> <br>" & "Part of the first-rate service I offer as a mortgage broker is checking in regularly with my clients and key contacts. I do this to ensure you’re kept up to date with mortgage market movements to help keep you two steps ahead. <br> <br>" & _
                    "With more and more of my clients now using an iPhone, I’m really pleased to let you know I now have my own broker app that I would love you to download to your phone.<br> <br>" & "The app has a host of tools and info at your fingertips. Staying in touch with me,finding out how much you could be saving on your loan, or if perhaps you’re thinking about a renovation or investment property the app can help give you an idea of how much you may be able to borrow. And if you know someone who may be needing help with their finance, you can share my app via email or a QR code. <br> <br>" & _
                    "You can check out more about how the app works here - www.mybrokerapp.com.au and you can download it by clicking on this " & "<a href=""http://itunes.apple.com/au/app/myafgbroker/id554555566?mt=8"">link here </a>. <br> <br>" & "When you’ve downloaded the app onto your iPhone open it up and <b>scan the QR code below</b> or <b>when viewing this email on your iPhone</b> " & _
                    "<a href=""brokerapp://register/?id=" & result & """>click this link</a>, either way will give you instant access to my app! <br> <br>" & "<img src='http://qrcode.kaywa.com/img.php?s=8&d=" & result & "'> <br> <br>" & "Having a mortgage broker go into bat for you with your finance is the smart way to ensure you’re in the right finance solution and you’re two steps ahead. <br> <br>" & "Staying in touch has now got smarter too. <br> <br>" & "Kind regards, <br> <br>" & "PS – I’m not one to often email you with news like this, but please do let me know simply by replying to this email if you’d prefer not to receive similar messages from me in the future. </html>"
    
      
    '    .SaveAs "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"
        .SaveAs "c:\test\" & result & ".msg"
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    
  
    With olMsg
        .To = BrokerEmail
        .Subject = "Your own iPhone app is ready to download and share with your clients!"
      .HTMLBody = "<img src='http://www.afgonline.com.au/afg_extranet/downloads/myAFG Broker Logo.JPG'>" & "<html><body style='font-family:Calibri;font-size:15px;'>" & "Dear " & BrokerName & "," & "<br> <br>" & "<b>Your broker branded iphone app is ready for you and your clients to download. </b> <br>" & "As you know, you’ve been chosen as one of the first AFG brokers to receive your version of the app. Welcome to your very own tailored and branded iphone app that AFG has developed and built just for you.  It is designed to give your clients all the tools and functionality they need to keep on top of their finance, and to keep your details top of mind, all at the tip of their fingers from their iphone, leveraging the shift to mobile technology. <br> <br>" & "<b> How can I see what the app can do? </b> <br>" & _
                    "The best way to see how the app works and just what it can do is by clicking on this clever site we’ve created at www.mybrokerapp.com.au. <br>" & "Do scroll down on the site and you will find three short videos that illustrate the power of the app, how easy it is to use, and what it can do for your business. <br>" & "Don’t forget, the app works best with a photo of yourself and the active links to your social media accounts such as facebook.  So, do make sure we have this information from you so that you’re taking advantage of as many of its features as possible. <br> <br>" & "<b>What information do I need to know?</b> <br>" & _
                    "All the questions you may have in relation to the app should all be answered in the FAQ document attached. <br> <br>" & "<b>How do I download my app onto my own iPhone?</b> <br>" & "Once you’ve visited the site above and read the first attachment, please click on your customer email above, where you can scan your QR code or click on your link to allow you to download your app onto your own iPhone. <br>" & "This will ensure you get to know it before you invite your customers to download it. <br> <br>" & "<b>How do I share the app with people and encourage my clients to use it?</b> <br>" & "We’ve attached an email template for you to use to send to your clients.<br>" & _
                    "Please just save this email template onto your computer and then once you open it up, it will launch in Outlook and you’ll be able to send to as many of your email contacts as possible.<br>" & "Of course there are also built in share features within the app itself, which is another way of promoting the app across your client base.<br> <br>" & "<b>It’s a new world, and we’re here to help you with any questions you may have.</b> <br>" & "If you have any questions on how to install the app or how to use it, please don’t hesitate to contact us and we can walk you through how it all works. <br> <br>" & "Kind regards, <br> <br>" & "AFG’s new way of working team </html>"
        .Attachments.Add "c:\test\" & result & ".msg"
'       .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Information Sheet - my AFG Broker App.pdf"
'       .Attachments.Add "N:\Commissions\Shared\Member Support\08 Projects\New Way of Working\myBroker App\Client Emails\" & result & ".msg"
        
        .Display
    End With
Next 'i
End Sub

This is my final code (still have to modify the paths though).

Again, thank you very much for this!

Regards,
Simon

**TMS** · 11-27-2012, 09:34 PM

You're very welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save