Adding Outlook Signature

[craigw02]

Hi Guys,

Im trying to add a signature to the code below which relates to ANYONE sending out this email so need the code to look at whoever would send this out and insert the correct signature? Any ideas?

Sub CreateMail()
    
    Dim objOutlook As Object
    Dim cell       As Range
    
    Set objOutlook = CreateObject("Outlook.Application")
        
    For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If UCase(cell.Range("G1").Value) = "YES" Then
            With objOutlook.CreateItem(0)
                .To = cell.Value
                .CC = cell.Range("C1").Value
                .Attachments.Add cell.Range("D1").Value
                .Subject = cell.Range("E1").Value
                .body = cell.Range("H1").Value
                .Display    'Instead of .Display, you can use .Send to send the email _
                             or .Save to save a copy in the drafts folder
            End With
        End If
    Next cell
    
    Set objOutlook = Nothing
    
End Sub

 

[BiocideJ]

I do something similar, however, I just keep the user's default signature (that must be set as the default that automatically occurrs on e-mails)

Sub CreateMail()
    
    Dim objOutlook As Object
    Dim cell       As Range
    
    Set objOutlook = CreateObject("Outlook.Application")
        
    For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If UCase(cell.Range("G1").Value) = "YES" Then
            With objOutlook.CreateItem(0)
                .To = cell.Value
                .CC = cell.Range("C1").Value
                .Attachments.Add cell.Range("D1").Value
                .Subject = cell.Range("E1").Value
                .Display     'Have to display first to capture existing signature
                .HTMLBody = cell.Range("H1").Value & .HTMLBody     'I always use HTMLBody for the body (I don't remember if this is necessary so you can test with .Body)
                End With
        End If
    Next cell
    
    Set objOutlook = Nothing
    
End Sub

 

[craigw02]

Hi There,

Thanks for the reply but just 1 problem that works great and adds the signature but the text is deleted and ONLY the signature appears in the email, when I changed it to read .body it inserted all the message and signature but WITHOUT the formatting, any way of fixing this so that the message and formatted signature both appear correctly?

Thanks

 

[daverunt]

Hi

You should use .HTMLBody for the signature
Insert Outlook Signature in mail

The Text would then need HTML tags. See Ron's example.

 

[craigw02]

Hi

You should use .HTMLBody for the signature
Insert Outlook Signature in mail

The Text would then need HTML tags. See Ron's example.

This is my code now, so how would I change it then?

Sub CreateMail2()
    
    Dim objOutlook As Object
    Dim cell       As Range
    
    Set objOutlook = CreateObject("Outlook.Application")
        
    
    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If UCase(cell.Range("G1").Value) = "YES" Then
            With objOutlook.CreateItem(0)
                .To = cell.Value
                .CC = cell.Range("C1").Value
                .Attachments.Add cell.Range("D1").Value
                .Subject = cell.Range("E1").Value
                .Display     'Have to display first to capture existing signature
                .htmlbody = cell.Range("H1").Value & .htmlbody   'I always use HTMLBody for the body (I don't remember if this is necessary so you can test with .Body)
                End With
        End If
    Next cell
    
    Set objOutlook = Nothing
    
End Sub

 

[BiocideJ]

I think to answer your question, you need to advise what 'text' is in Cell H1 that you are trying to add.

 

[craigw02]

I think to answer your question, you need to advise what 'text' is in Cell H1 that you are trying to add.

The text "varies" from line to line so its not going to be the same sentences for every email!!

 

[BiocideJ]

I get that, but can you give us an example of exactly what you are trying to insert so we can test.
Mainly, I want to confirm that it is strictly text and not some type of encoded information.

 

[craigw02]

Hi there,

It would be something along these lines "Please Find enclosed Remittance advice for the Period 1/4/14 - 14/4/14, please complete and return, Thanks".

Again as I said dates could be different or added wording for returns not been sent in etc

Thanks

 

[BiocideJ]

OK, I am testing and it works exactly as I would expect on my system.

What version of Excel and Outlook are you using and what exactly is happening currently.