Email Confirmation from VBA Macro

kraamerica

Board Regular
Joined
Apr 7, 2020
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I have a macro that I run that sends specific information and attachments that are compiled from another worksheet and copied to this one. What I'm looking for is "another" email (confirmation) that when the macro is run and the data is sent out from Outlook, that a singular confirmation email be sent to a specific email address that shows contains a specific subject line (with date in that line if possible), brief description in the body, date (if not possible in Subject line and # of records sent. Here is my current macro:

Sub SendPFRCemail()
Dim edress As String
Dim subj As String
Dim name As String
Dim message As String
Dim filename, filename2 As String
Dim logo As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path, path2, path3 As String
Dim attachment As String
Dim lastrow As Integer
Dim x As Integer


x = 2
Do While Sheets("Sheet1").Cells(x, 1) <> ""

Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments
path = "C:\Users\tziska\Google Drive\PFRC Shortcut Files\"
'path = "C:\Users\screa\OneDrive\reconstructionBH\Documents\contracts\tracy\april13\"
'path2 = "C:\Users\tziska\Google Drive\PFRC Shortcut Files\"
'path3 = "C:\Users\tziska\Google Drive\PFRC Shortcut Files\"
Application.DisplayAlerts = False
edress = Sheets("Sheet1").Cells(x, 9)

subj = "Upcoming Budget Truck Rental Reservation" & " " & ref
ref = Sheets("Sheet1").Cells(x, 4)
dealer = Sheets("Sheet1").Cells(x, 14)
Location = Sheets("Sheet1").Cells(x, 12)
name = WorksheetFunction.Proper(Sheets("Sheet1").Cells(x, 7))
filename = "Personal Financial Responsibility Certificate.pdf"
filename2 = "Insurance Agent Toll Free Numbers.pdf"
logo = "BTRlogo.png"

attachment1 = path + filename
attachment2 = path + filename2
'attachment = path + logo

outlookmailitem.to = edress
outlookmailitem.cc = ""
outlookmailitem.bcc = ""
outlookmailitem.Subject = subj & ref
'outlookmailitem.Attachments.Add path & logo, 0
strbody = "Dear" & " " & name & "," _
& "<p><b><i>Thank you for choosing Budget Truck Rental.</i></b> We look forward to seeing you soon. Here are a few items of importance to consider before your moving day!</p>" _
& "<p><b>COVERAGE</b> - Our goal at Budget is to be certain you are well informed of your options and choices with your insurance provider and Budget. We offer several discounted Protection Package options to best meet your needs. </p> " _
& "<p><p style='margin-left:36.0pt'>1. <b>Complete Protection Package</b> - covers the truck 100%, includes extended roadside service, provides up to $1 million in liability protection and covers you and your personal belongings for a worry free, peace of mind rental experience. </p> " _
& "<p><p style='margin-left:36.0pt'>2. <b>Choice Protection Package</b> - covers the truck 100%, includes extended roadside service and provides up to $1 million in liability protection. </p> " _
& "<p><p style='margin-left:36.0pt'>3. <b>Value Protection Package</b> - covers the truck 100% and includes extended roadside service. </p> " _
& "<p>The truck you are renting may weigh over 10,000 pounds and may have 6 wheels. Because the rental truck may be commercially rated as a non-passenger vehicle with a separated cargo space, most personal auto insurance policies do not extend coverage. We have attached a <b>Personal Financial Responsibility Certificate</b> form to assist your understanding of insurance needs regarding the rental of a Budget moving truck. </p>" _
& "<p><u>Please contact your personal auto insurance claims office and bring the attached completed form to the location on the day you pick up your truck</u>. Individual insurance policies vary greatly and agent offices aren’t necessarily well versed with the nuances of all their client’s policies, so the claims office is your best resource. A claims agent will be able to provide you with accurate information when they know specific information about the rental truck; gross vehicle weight, number of wheels, value and that it is a commercial non-passenger vehicle with a separated cargo space. </p>" _
& "<p><b><i> When opting to self-insure for damage and collision, individually or with your insurance provider, you agree to payment on demand for all loss or damage to the truck whether or not you are at fault from any cause. As such, you accept responsibility for subrogating all claims with your insurance provider. If your insurance provider states that your policy extends to a moving truck, be certain to obtain the claims agent's name, telephone number and date/time spoken to. In the event of a claim denial, you will need this information to facilitate an errors and omissions claim with your insurance provider. The following credit cards most likely do not extend damage or liability coverage for cargo trucks or vans; American Express, Visa, MasterCard, Discover.</b></i></p>" _
& "<p>Learn more from the insurance and moving professionals: </p>" _
& "<p><p style='margin-left:50.0pt'><a href= ""https://www.allstate.com/tr/car-insurance/renting-moving-truck.aspx"">Allstate </a></p>" _
& "<p><p style='margin-left:50.0pt'><a href= ""Should You Buy Rental Truck Insurance? | Moving.com"">Moving.com </a></p>" _
& "<p>If you would like a quote for Budget Truck Rental’s optional coverage products, please reply to this email noting your move date or you can go into your reservation at " & "<a href= ""Budget Truck Rental"">www.budgettruck.com/reservations </a> and make any necessary changes. Additional information regarding Budget Truck Rental’s optional coverage products can be found here at " & "<a href= ""Budget Truck Rental"">Budget Truck Optional Protection </a>.</p>" _
& "<p><u><b>PACKING & LOADING<b></u> - Also, don't forget to reserve moving accessories; furniture pads to protect your valuables, a hand truck for both appliances and boxes. </p>" _
& "<p>Please contact your pick up location directly or Customer Service at (800) 283-4382 for answers to any of your questions or reservation changes. We appreciate the opportunity to service your business and look forward to assisting you with your move!! </p>" _
& "<p>" & dealer _
& "<br>" & Location _
& "<p>We appreciate the opportunity to serve you!</p>" _
& "<p><img src='BTRlogo.png'" & "width='366' height='100'></p>"

logo = "BTRlogo.png"
subj = subj & " " & ref
With outlookmailitem
.to = edress
.HTMLBody = strbody & "<br>" & .HTMLBody
.Attachments.Add path & logo, 0
'.Attachments.Add (attachment)
End With

myAttachments.Add (attachment1)
myAttachments.Add (attachment2)

'myAttachments.Add path & logo, 0
'outlookmailitem.display
outlookmailitem.send
'End With


lastrow = lastrow + 1
edress = ""
ref = ""
x = x + 1

Loop


Set outlookapp = Nothing
Set outlookmailitem = Nothing

End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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
Back
Top