VBA code to produce Outlook mail not adding auto signature

[Twaddy006]

Evening all

I have a user form which prior to my company upgrading to Windows 7 from XP worked perfectly, we're using Office 2010

The outlook mail previously populated the contents of the user form, added the distribution list, mail title, body of the mail and also the auto signature of whoever was using it

Now it does everything bar add the auto sig. From memory and my basic understanding, the .display prior to .to = MailList meant this was possible, the majority of code was taken from Ron de Bruin's code and adapted to suit

Can anyone see anything within the code that would cause this or have any suggestions to fix?

Many thanks

Sub EmailMeNewVDN()

    Dim Cell     As Range
    Dim MailList As Variant
    Dim Rng      As Range
    Dim Wks      As Worksheet

    Dim OlApp    As Object
    Dim O1Mail   As Object
    Dim OlNS     As Object
    Dim HTMLbody As String

        Set Wks = Workbooks("Change Notification Form 5.0.xlsm").Sheets("Email List")
        Set Rng = Wks.Range("A1", Wks.Cells(Rows.Count, "A").End(xlUp))
        
        For Each Cell In Rng
            MailList = MailList & Cell.Value & ";"
        Next Cell

        Set OlApp = CreateObject("Outlook.Application")
        Set OlNS = OlApp.Session.GetDefaultFolder(6)
       
    Dim iOrderIT As String, iCustomer As String, iText34 As String, iChangeType As String
    Dim iArea1 As String, iArea2 As String, iText29 As String, iText4 As String, iText5 As String, iText8 As String, iText9 As String
    Dim iText12 As String, iComboBox3 As String

        iOrderIT = UserFormChangeNotification.TextBox1.Text
        iCustomer = UserFormChangeNotification.TextBox2.Text
        iText34 = UserFormChangeNotification.TextBox34.Text
        iChangeType = UserFormChangeNotification.ComboBox1.Text
        iArea1 = UserFormChangeNotification.ComboBox2.Text
        iArea2 = UserFormChangeNotification.ComboBox3.Text
        iText4 = UserFormChangeNotification.TextBox4.Text
        iText5 = UserFormChangeNotification.TextBox5.Text
        iText8 = UserFormChangeNotification.TextBox8.Text
        iText9 = UserFormChangeNotification.TextBox9.Text
        iText12 = UserFormChangeNotification.TextBox12.Text
        iText29 = UserFormChangeNotification.TextBox29.Text
        iComboBox3 = UserFormChangeNotification.ComboBox3.Text
   
        HTMLbody = "<FONT color=#000000 face=Arial size=2>" & "Hello all" & "<br>" & "<br>" & "Please find below details of a scheduled change" _
    & "<br>" & "<br>" & "OrderIT Reference :" & " " & iOrderIT _
    & "<br>" & "Customer :" & " " & iCustomer _
    & "<br>" & "<b>Scheduled Change Date :</b>" & " " & iText34 _
    & "<br>" _
    & "<br>" & "<b>Change Type :</b>" & " " & iChangeType _
    & "<br>" _
    & "<br>" & "Area :" & " " & iArea1 _
    & "<br>" & "VDN Number :" & " " & iText29 _
    & "<br>" & "VDN Name :" & " " & iText4 _
    & "<br>" & "Parent Group :" & " " & iText5 _
    & "<br>" _
    & "<br>" & "<b>Additional Information :</b>" _
    & "<br>" & iText12 _
    & "<br>" _
    & "<br>" & "Please email <A href=mailto:$TB%20-%20Telephony%20Routing%20Support%20&%20Solutions>$TB - Telephony Routing Support & Solutions</A> if you wish to unsubscribe" _
    & "<br>" _
    & "<br>" & "Many thanks"
                
            With OlApp.CreateItem(0)
                .display
                .to = MailList
                .CC = ""
                .BCC = ""
                .Subject = "Telephony Routing Change Notification" & " " & "-" & " " & "New VDN"
                .HTMLbody = HTMLbody
                .display
            End With
            
      Set OlMail = Nothing
    Set OlApp = Nothing
    
End Subv

 

[DaveBlakeMAAT]

I think the signature is added to the body on the display line, so when you are adding your HTMLBody you are over writing the signature, by using .HTMLbody=HTMLbody & "
" & .body you should maintain the signature.

I hope this helps.

Regards

Dave


With OlApp.CreateItem(0)
.BodyFormat = olFormatHTML 'Incase HTML is not the default email format
.display
.to = MailList
.CC = ""
.BCC = ""
.Subject = "Telephony Routing Change Notification" & " " & "-" & " " & "New VDN"
.HTMLbody = HTMLbody & "
" & .body
'.display <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<comment out="" this="" .display="" as="" you="" have="" it="" above<="" font=""></comment>
End With

Set OlMail = Nothing
Set OlApp = Nothing

End Sub

 

[Twaddy006]

Hi Dave

Thanks for your reply

I'm not having any joy unfortunately, it debugged at ".BodyFormat = olFormatHTML" initially, so I commented that out, then it ran however with a blank email body which I what I was getting before the signature issue, if I change the code back to .HTMLbody = HTMLbody from .HTMLbody = HTMLbody & .body, it works in that the email body populates but the signature is left off

Any further thoughts?

Many thanks

 

[DaveBlakeMAAT]

Right not sure, and I am by no means an expert, but try the following:

.bodyformat = 2 should fix the debug

change your declared variable from "HTMLbody" to "MyHTMLbody" (as it is ambiguous, Don't forget to update where you define the variable as well MyHTMLbody = "" & "Hello all" &...)

then do

.HTMLBody = MyHTMLbody & "
" & .HTMLbody.

 

[Twaddy006]

Still no joy I'm afraid after trying your suggestions, doesn't seem to matter what I try, I can either get the body of the mail correct without a signature or vice versa

For now at least I'm going to have to go for the non signature version and people can add their own manually, not perfect but I need to get the form out to be used

In the meantime I'll keep searching for a solution, there must be one

Thanks again for your suggestions Dave, anymore would be very welcome

Cheers

 

[DaveBlakeMAAT]

Hi

I have now had the chance to drop your code into a procedure, obviously I have removed your lookups just for testing purposes.

Try dropping this into a new module and step through the code using F8 and see if this does what you need.

Sub EmailMeNewVDN()

    Dim Cell     As Range
    Dim MailList As Variant
    Dim Rng      As Range
    Dim Wks      As Worksheet


    Dim OlApp    As Object
    Dim O1Mail   As Object
    Dim OlNS     As Object
    Dim [COLOR=#ff0000]MyHTMLbody[/COLOR] As String

        Set OlApp = CreateObject("Outlook.Application")
        Set OlNS = OlApp.Session.GetDefaultFolder(6)
       
    Dim iOrderIT As String, iCustomer As String, iText34 As String, iChangeType As String
    Dim iArea1 As String, iArea2 As String, iText29 As String, iText4 As String, iText5 As String, iText8 As String, iText9 As String
    Dim iText12 As String, iComboBox3 As String
   
        [COLOR=#ff0000]MyHTMLbody[/COLOR] = "" & "Hello all" & "" & "" & "Please find below details of a scheduled change" _
    & "" & "Many thanks"
                
            With OlApp.CreateItem(0)
[COLOR=#ff0000]                .bodyformat = 2[/COLOR]
                .display
                .to = "anyone@anywhere.com"
                .CC = ""
                .BCC = ""
                .Subject = "Telephony Routing Change Notification" & " " & "-" & " " & "New VDN"
                .HTMLbody = [COLOR=#ff0000]MyHTMLbody & .HTMLbody[/COLOR]
            End With
            
      Set OlMail = Nothing
    Set OlApp = Nothing
    
End Sub

 

[Twaddy006]

Hi Dave

No joy I'm afraid, it's debugging at

.HTMLbody = MyHTMLbody & .HTMLbody

Thanks again for taking the time to look at it