Excel Macro To Send Specific Info In Multiple Separate Emails
<p>So I'm in need of some help. I'm trying to update my company's Excel tool that they use to manage clients. I currently need help with updating the Send Email function of the tool. So employees will have a list of clients they need to send an email to, and each client will have specific information like name, client number, etc., and the employees should be able to click the "Send Email" macro button that's built in, and different emails will populate in Outlook with the specific info for each client built into the Email recipient, Title and Body. </p>

<p>I am currently stuck. For example, I will choose 3 clients to send an email to, click the "Send Email" button, and I will get 3 emails to populate (as I should). The first email will have all of the correct info in the right place for Client #1. However, Email #2 will have the correct Email Recipient &amp; Email Title, but then the Email Body will have the correct info for Client #2, and below that also in the Email Body will be all of the email body info for Client #1. And same for Email #3, it will have the correct recipient and title, but the Body will have body info for Client #3, then Client #2, then Client #1. </p>

<p>So I know that I need to fix it through some sort of loop for the email body, but I have been working on this for so long I just can't see it anymore. I've removed any sensitive information and put in placeholders, but I think you should get the idea.</p>

<p>Please let me know if you have any questions. </p>

<pre><code>Sub SendEMail()
Dim Email As String
Dim Subj As String
Dim Msg As String
Dim URL As String
Dim r As Integer
Dim x As Double
Dim OApp As Object
Dim OMail As Variant
Dim Signature As String
Dim strbody As String



strbody = "&lt;html&gt;&lt;body&gt;"

With Sheets("Email").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
End With

For r = 2 To lastrow


Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)

'       Get the email address
Sheets("Email").Select
Email = Cells(r, "F")
'       Message subject
Sheets("Email").Select
Subj = "Renewal for " &amp; Cells(r, "B").Text &amp; " Contract " &amp; Cells(r, "A").Text &amp; " Effective " &amp; Cells(r, "C").Text

'       Message body
Sheets("Email").Select
strbody = strbody &amp; "Dear " &amp; Cells(r, "AR").Text &amp; ", &lt;br&gt;&lt;br&gt;" &amp; _
"I will be working with you on " &amp; Cells(r, "B") &amp; ", client number " &amp;       Cells(r, "A") &amp; ", which is effective " &amp; Cells(r, "C") &amp; ".&lt;br&gt;&lt;br&gt;" &amp; _
"For this year's contract, we are requesting the following information: &lt;br&gt;" &amp; _
"&lt;li&gt;" &amp; Cells(r, "AH") &amp; "&lt;/li&gt;" &amp; "&lt;br&gt;&lt;br&gt;" &amp; _
"The application form may be downloaded from:&lt;br&gt;" &amp; _
"&lt;li&gt;Option #1&lt;/li&gt;: " &amp; "&lt;a href=""" &amp; "Link#1" &amp; """&gt;" &amp;    "Link#1" &amp; "&lt;/a&gt;" &amp; "&lt;br&gt;" &amp; _
"&lt;li&gt;Option #2&lt;/li&gt;: " &amp; "&lt;a href=""" &amp; "link#2" &amp; """&gt;" &amp; "link#2" &amp; "&lt;/a&gt;" &amp; "&lt;br&gt;&lt;br&gt;" &amp; _
"Once we receive the requested information, you will receive your contract within 5 business days. Should you have any questions, please don't hesitate to contact me at this email address or phone number &lt;br&gt;&lt;br&gt;" &amp; _
"As always, we would like to thank you for your business. &lt;br&gt;&lt;br&gt;" &amp; _
"Regards, &lt;br&gt;"

On Error Resume Next

Sheets("Email").Select
With OMail
.Display
.To = Email
.Subject = Subj
.HTMLBody = strbody &amp; vbNewLine &amp; .HTMLBody
End With
Next r

On Error GoTo 0

Set OMail = Nothing
Set OApp = Nothing

End Sub
</code></pre>