Mail every worksheet with Different signature use excel vba

Question

i need your help the below code work to send email for the sheets my question ? how i can change the signature automated ? i have the name of the signature in the excel file lets call it (b2) . its possible to make it ? Note : i use excel 365 and widows 10

Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
 TempFilePath = Environ$("temp") & "\"

        'You use Excel 2007-2016
        FileExtStr = ".xls": FileFormatNum = 52


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ThisWorkbook.Worksheets

        If sh.Range("A2").Value Like "?*@?*.?*" Then

           sh.Copy
            Set wb = ActiveWorkbook

            TempFileName = sh.Name

            Set OutMail = OutApp.CreateItem(0)


            With wb

               .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

                On Error Resume Next

                With OutMail

                .Attachments.Add wb.FullName

                .Display

                strbody = "HI sony "



                    .to = sh.Range("A2").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = "This is the Subject line"
                    .HTMLBody = "HI sony " & "<br>" & .HTMLBody
                    .Send



                    'You can add other files also like this
                    '.Attachments.Add ("C:\test.txt")


                End With
                On Error GoTo 0

                .Close savechanges:=False
            End With

            Set OutMail = Nothing

            Kill TempFilePath & TempFileName & FileExtStr

        End If
    Next sh

    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

score 0 · Answer 1 · answered Jan 10 '19 at 13:26

If you just want to add the default signature then display the email before sending it:

.Display
.HTMLBody = strbody & "<br>" & .HTMLBody
.Send

If, however, you want to use a specific signature file then you will need to read that file:

SigString = Environ("appdata") & "\Microsoft\Signatures\B2.htm"
If Dir(SigString) = "" Then
    OutSignature = ""
Else
    Dim fso As Object
    Dim sf As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set sf = fso.GetFile(SigString).OpenAsTextStream(1, -2)
    OutSignature = sf.readall
    sf.Close
End If
.HTMLBody = strbody & "<br>" & OutSignature
.Send

I use Excel 2013 though this answer should not be restricted to that version.

Mail every worksheet with Different signature use excel vba

1 Answers1