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; 3 weeks ago. Reason: My code displays here horizontally but I wrote it vertically,I do not know the reason

  • #2
    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
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      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.

      Comment


      • #4
        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
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


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

          Comment


          • #6
            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 ...
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              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?

              Comment


              • #8


                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
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment

                Working...
                X