Is there a way to code in VBA the subject line of an outlook email to correspond to multiple parts being pulled from different cells?

Question

Basically want the coding to work for this format of a subject line of an email:

Group name - Office - Sales Rep - Due Date

C3 - C4 - C5 - C6 - C7

So the info entered into the above cells all show up in the subject line. I currently just have it coded to be changed once the email pops up:

.Subject = "Group Name-Office-Rep-Lives-MM/DD/YY: Life&Disability Proposal Notification"

Is this possible? If so, please help. Thanks!

Do you mean `... = Range("C3") & "-" & Range("C4") & "-" & Range("C5") & ... & ": Life&Disability Proposal Notification"`? — BruceWayne, Jan 22 '20 at 21:46
Yes, you can (build as string how you would elsewhere as above) If you have other issues, here is a email post of mine earlier. Fully working email from excel macro in post https://stackoverflow.com/questions/59866738/email-macro-pauses-every-40-50-emails?noredirect=1#comment105865169_59866738 — urdearboy, Jan 22 '20 at 21:52

score 0 · Accepted Answer · answered Jan 22 '20 at 21:54

Here is what I came up with. It depends on your ranges if they are constant or not that will change your code a little bit.

Dim objNewWorkbook As Excel.Workbook
Dim objNewWorksheet As Excel.Worksheet
Dim objOutlookApp As Object
Dim objMail As Object
Dim group As String
Dim office As String
Dim salesrep As String
Dim mydate As String
mydate = Format(Date, "mm/dd/yy")

group = Range("c3")
office = Range("c4")
salesrep = Range("c5")


  On Error Resume Next
       Set objOutlookApp = CreateObject("Outlook.Application")
       Set objMail = objOutlookApp.CreateItem(olMailItem)

       'Set Email
       With objMail
           '.To = "Test"
           .To = "Test"
           .CC = ""
           .Subject = "" & group & "  " & office & "  " & salesrep & "  " & mydate & ""
           .body = "Good Morning. " & vbCrLf & vbCrLf & ""
           '.Attachments.Add ActiveWorkbook.FullName
           .display
        End With

Is there a way to code in VBA the subject line of an outlook email to correspond to multiple parts being pulled from different cells?

1 Answers1