Announcement

Collapse
No announcement yet.

Body does not appear when sending outlook mails with signature with excel macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Body does not appear when sending outlook mails with signature with excel macro

    Since I am new to the VBA I am trying to structure a code to send email through outlook with the signature.But when I run the below code it displays me only the signature(body does not appear).

    When I use "F8" and check it can be clearly seen that my body appears then signature overwrites it.I tried with a lot of experts' answers but it remains with only the signature or only the body.How do I fix this?

    Code:
    Option Explicit  Sub SendEmailwithsign()  Dim objoutApp As Object, objoutmail As Object Dim strbody As String, strSig As String Set objoutApp = CreateObject("outlook.Application") Set objoutmail = objoutApp.CreateItem(0)  On Error Resume Next  With objoutmail    .To = "[email protected]"   .CC = ""   .Subject = "Test mail"   .Recipients.ResolveAll   .Display                 'body appears until this point'    strSig = .Environ("appdata") & "Microsoft\Signatures\bbbb.txt"   strbody = "Hello"   .body = strbody & strSig     'with this step Body part does not appear but only the signature'    End With     On Error GoTo 0   Set objoutmail = Nothing   Set objoutApp = Nothing  End Sub
    Last edited by NILUSHA MADUMAL; November 21st, 2018, 13:40. Reason: My code displays here horizontally but I wrote it vertically,I do not know the reason

  • Carim
    replied
    Hello,

    Thanks for your explanation ...

    Regarding the problem of the signature in html format..., a couple of remarks :

    1. strSig has to be defined in the html format ... with the extension .html

    2. You have to avoid the signature error with

    Code:
    ' To avoid a Signature error ...
     
        If Dir(strSig) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If
    which uses the function GetBoiler :

    Code:
    Private Function GetBoiler(ByVal sFile As String) As String
    ' Function needed to extract the HTML signature
        Dim FSO As Object
        Dim ts As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set ts = FSO.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
    End Function
    Only after this process you can have your macro changed to show :

    Code:
       .HTMLBody = strbody & Signature
    Hope this will help

    Leave a comment:


  • NILUSHA MADUMAL
    replied
    Yes you are correct.The problem I am facing is both body and signature do not appear together..the code you privide me gives only the body part.what is happening is if you can press F8 and see first signature appears when it goes through "HTMLbody" part of the code signature suddenly disappears and is overwritten by the body.finaly it remains with only the body.How do I make it to appear both body and signature?

    Leave a comment:


  • Carim
    replied
    Hello,

    The title of your thread is
    Body does not appear ...
    and in your last message ...
    it displays only the body.
    Thanks for your clarification ...

    Leave a comment:


  • NILUSHA MADUMAL
    replied
    Unfortunately same problem occurs.I tried with your code but it displays only the body.I do not what the wrong is.

    Leave a comment:


  • Carim
    replied
    Hello,

    Below is an example ...

    Code:
    Option Explicit
    
    Sub SendEmailwithsign()
    
    Dim objoutApp As Object
    Dim objoutmail As Object
    Dim strbody As String
    Dim strSig As String
    Set objoutApp = CreateObject("outlook.Application")
    Set objoutmail = objoutApp.CreateItem(0)
    On Error Resume Next
      With objoutmail
          .To = "[email protected]"
          .CC = ""
          .Subject = "Test mail"
          .Recipients.ResolveAll
          strSig = .Environ("appdata") & "Microsoft\Signatures\bbbb.txt"
          strbody = "<body>" & "Hello " & "," & "<br><br>" & _
                "Please find attached your updated report." & "<br><br>" & _
                "It reflects the situation as of : " & Format(Date, "dd-mmm-yy") _
                & "  , for all your area of responsibility." & "<br><br>" & _
                "Could you please review it." _
                & "<br><br>" & _
                "Let me know if I can be of any help." & "<br><br>" & _
                "Best Regards," & "<br><br>" & _
                "John" & "<br><br>" & "</body>"
          .HTMLBody = strbody & strSig
          .Display
      End With
    On Error GoTo 0
    Set objoutmail = Nothing
    Set objoutApp = Nothing
    End Sub
    Hope this will help

    Leave a comment:


  • NILUSHA MADUMAL
    replied
    Yesthank you for answering my problem.,i have already tried with these codes not only from Rondebruin but from other sites too.But unfortunately same issue occurs wtever the code I use.I would like to know the root cause for that if you have any clue on that matter.

    Leave a comment:


  • Carim
    replied
    Hello,

    Ron de Bruin has created a complete series of readily available macros ...

    see https://www.rondebruin.nl/win/s1/outlook/amail4.htm

    Hope his will help

    Leave a comment:

Working...
X