VBA Email with customized body based on sender

[MCTampa]

All,

I am using the following script to send a notification email to my team when a report is updated:

Sub Make_Outlook_Mail_With_File_Link()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        strbody = "<font size=""3"" face=""Calibri"">" & _
                  "Team,<br><br>" & _
                  "The Mid-Day Getaway Report has been updated and can be found here:<br><B>" & _
                  ActiveWorkbook.Name & "</B> is created.<br>" & _
                  "Click on this link to open the file : " & _
                  "<A HREF=""file://" & ActiveWorkbook.FullName & _
                  """>O:\report</A>" & _
                  "<br>Regards,<br>" & _
                  "<br>Mike<br></font>"
        On Error Resume Next
        With OutMail
            .To = user@domain.com
            .CC = ""
            .BCC = ""
            .Subject = "Mid-Day Report Updated:" & " " & Format$(Date, Now)
            .HTMLBody = strbody
            .Send
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
End Sub

Here is the issue - I created the report, so I had the body of the email end with "Regards, Mike".

However I have handed this report off to other members of the team. When they run the report and send it, it still says "Regards, Mike".

I would like the body to read who the sender is - I assume based on the from address - and have the body say "Regards, Kristine" or "Regards, Ana".

Is this even possible? I would think I need to make the sender a variable, but I do not know how to do that.

Thanks,
Mike

 

[igold]

Re: VBA Email with customized boday based on sender

Hi Mike,

When I copy your code to my worksheet, all of this section is an error:

" & _
"The Mid-Day Getaway Report has been updated and can be found here:
" & _
ActiveWorkbook.Name & " is created.
" & _
"Click on this link to open the file : " & _
" """>O:\report" & _
"
Regards,
" & _
"
[COLOR=#ff0000]Mike[/COLOR]
"

That being said, how about substituting Mike with this line:

Dim sndr As String
sndr = Left(Application.UserName, InStr(Application.UserName, " ") - 1)

HTH

igold

 

[MCTampa]

Re: VBA Email with customized boday based on sender

Thanks igold - but I'm getting the error: Expected: end of statement for the last line. It won't accept a parenthesis or anything else I try to use to end the last line.

Sub Make_Outlook_Mail_With_File_Link()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sndr As String
    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        strbody = "<font size=""3"" face=""Calibri"">" & _
                  "Team,<br><br>" & _
                  "The Mid-Day Getaway Report has been updated and can be found here:<br><B>" & _
                  ActiveWorkbook.Name & "</B> is created.<br>" & _
                  "Click on this link to open the file : " & _
                  "<A HREF=""file://" & ActiveWorkbook.FullName & _
                  """>O:\Mike Tampa\Mid-Day Report\Mid-Day Report.xlsm</A>" & _
                  "<br>Regards<br>" & _
                  "<br>sndr = Left(Application.UserName, InStr(Application.UserName, " ") - 1)<br></font>"

        On Error Resume Next
        With OutMail
            .To = "michael.tampa@intervalintl.com"
            .CC = ""
            .BCC = ""
            .Subject = "Mid-Day Report Updated:" & " " & Format$(Date, Now)
            .HTMLBody = strbody
            .Send
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
End Sub

 

[igold]

Re: VBA Email with customized boday based on sender

Hi,

I am not sure where you are getting the error, could you be more specific. Alternatively you may want to try to write a new one line sub with the code I posted and see if you get the same error or just returns the users first name.

igold

 

[MCTampa]

Re: VBA Email with customized boday based on sender

My issue is that I'm not sure how to insert your code.

I'm trying the following:

Sub Make_Outlook_Mail_With_File_Link()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sndr As String


    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        strbody = "<font size=""3"" face=""Calibri"">" & _
                  "Team,<br><br>" & _
                  "The Mid-Day Getaway Report has been updated and can be found here:<br><B>" & _
                  ActiveWorkbook.Name & "</B> is created.<br>" & _
                  "Click on this link to open the file : " & _
                  "<A HREF=""file://" & ActiveWorkbook.FullName & _
                  """>O:\Mike Tampa\Mid-Day Report\Mid-Day Report.xlsm</A>" & _
                  "<br>Regards<br>" & _
                  "<br>sndr = Left(Application.UserName, InStr(Application.UserName, " ") - 1)<br></font>"

But clearly that is wrong and every attempt I've made to adjust it has proved to not work.

 

[igold]

Re: VBA Email with customized boday based on sender

Having posting problems with HTML code embedded in VBA code

Sub Make_Outlook_Mail_With_File_Link()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sndr As String




    If ActiveWorkbook.path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        sndr = Left(Application.UserName, InStr(Application.UserName, " ") - 1)


        strbody = "Team," & "<br><br>" & _
        "The Mid-Day Getaway Report has been updated and can be found here: " & ActiveWorkbook.Name & " is created." _
        & "<br><br>" & "Click on this link to open the file: " & "<A HREF=file://" & ActiveWorkbook.FullName & """>Link to the file</A>" _
        & "<br><br>" & "Regards," & "<br><br>" & sndr
        


        With OutMail
            .to = "user@domain.com"
            .Subject = "Mid-Day Report Updated:" & " " & Format$(Date, Now)
            .htmlbody = strbody
            .display
        End With
    End If
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    'Set rngTo = Nothing
    'Set rngSubject = Nothing
    'Set rngBody = Nothing
    'Set rngAttach = Nothing


End Sub

HTH

igold

 

[igold]

Re: VBA Email with customized boday based on sender

@Rory

Thanks for the edit!

igold