Concatenate String Values & Add punctuation

[kraamerica]

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.

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

 

[Eric W]

Welcome to the forum!

Change these 2 lines:

    strBody = "Hi " & Fullusername & vbCrLf & vbCrLf
    strBody = strBody & "I am a Territory Performance Manager covering " & city & State & vbCrLf & vbCrLf

to

    strBody = "Hi " & WorksheetFunction.Proper(Fullusername) & "," & vbCrLf & vbCrLf
    strBody = strBody & "I am a Territory Performance Manager covering " & WorksheetFunction.Proper(city) & ", " & State & vbCrLf & vbCrLf

 

[kraamerica]

Welcome to the forum!

Change these 2 lines:

    strBody = "Hi " & Fullusername & vbCrLf & vbCrLf
    strBody = strBody & "I am a Territory Performance Manager covering " & city & State & vbCrLf & vbCrLf

to

    strBody = "Hi " & WorksheetFunction.Proper(Fullusername) & "," & vbCrLf & vbCrLf
    strBody = strBody & "I am a Territory Performance Manager covering " & WorksheetFunction.Proper(city) & ", " & State & vbCrLf & vbCrLf

Oh man thank you!

You dont have any quick tips on inserting my outlook signature do you? Or at least a company logo?

 

[Eric W]

Sorry! I don't know how to do either of those off the top of my head. If I figure it out, I'll let you know.

 

[kraamerica]

Sorry! I don't know how to do either of those off the top of my head. If I figure it out, I'll let you know.

10-4 and thanks again for the help. I'll keep searching as well.