kraamerica
Board Regular
- Joined
- Apr 7, 2020
- Messages
- 56
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I'm trying to set up a macro for a my coworker (and eventually me) that will send multiple emails to people with specific data. I have almost everything to work except for the following:
1. I can't add punctuation (a comma) after the Customers name in the greeting
2. I can't concatenate 2 values (city, state) and keep in the same same string (also by separating city & state with a comma and adding a period at the end.
3. I would also like to be able to put a PROPER function in for the Fullusername & City to keep the end user from having to do so. I'm just not sure where to put that function code in the module.
I go on furlough in a couple days and thought I could eventually search my way through to the answer, but I'm running out of time. Appreciate any help and thank you. PS. I couldn't upload the spreadsheet, but would be happy to do so if that helps further.
1. I can't add punctuation (a comma) after the Customers name in the greeting
2. I can't concatenate 2 values (city, state) and keep in the same same string (also by separating city & state with a comma and adding a period at the end.
3. I would also like to be able to put a PROPER function in for the Fullusername & City to keep the end user from having to do so. I'm just not sure where to put that function code in the module.
I go on furlough in a couple days and thought I could eventually search my way through to the answer, but I'm running out of time. Appreciate any help and thank you. PS. I couldn't upload the spreadsheet, but would be happy to do so if that helps further.
VBA Code:
Sub send_canc_report_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' Subject
strSubj = "Reservation Cancellation"
On Error GoTo dbg
' Create a new Outlook object
Set olApp = CreateObject("Outlook.Application")
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
' Create a new item (email) in Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 5).Value
Fullusername = Cells(iCounter, 2).Value
city = Cells(iCounter, 21).Value
State = Cells(iCounter, 22).Value
reference = Cells(iCounter, 1).Value
'Make the body of an email
strBody = "Hi " & Fullusername & vbCrLf & vbCrLf
strBody = strBody & "I am a Territory Performance Manager covering " & city & State & vbCrLf & vbCrLf
strBody = strBody & "I'm following up on a notice I received that you cancelled your reservation. " & vbCrLf
strBody = strBody & "I look forward to hearing from you and I also thank you for your time!" & vbCrLf & vbCrLf
strBody = strBody & "PS. I am an area representative and my personal cell phone # is 515-222-2222. Please mention the following reference number " & reference & vbCrLf & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 2
' 1 – text format of an email, 2 - HTML format
olMailItm.Body = strBody
olMailItm.Send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description
End Sub