Adding a signature to email

[rywello]

That code just brings back a blank e-mail.

 

[rywello]

I don't get an error but it does not add the signature.

 

[Minty]

Does the user concerned (you ? ) have a default signature set ?

If they don't you will simply get the email with whatever your variables are set to, and no signature.
Please note that mine is a generic code - you'll need to adapt it for your body txt and other variables.

 

[rywello]

Yes, I can get the body text with the code I have. I have my default signature set but it does not want to add the signature with the code. Not sure what I am doing wrong.

 

[Minty]

So is your initial outlook bit like this to capture the signature?

Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail        ' This creates a blank email and captures the users default signature.
        .BodyFormat = olFormatHTML
        .Display
    End With

    signature = OutMail.HTMLBody

And you are then adding the variable signature back to your formatted body how?

 

[rywello]

That part of the code works but when I add the below code, it ignores the signature.

With OutMail

.To = Forms!Main!TxtEmailTo
.CC = Forms!Main!txtCCTo
.BCC = Forms!Main!txtBCCTo
.Subject = txtSubjectHeading
.HTMLBody = strFntNormal & BodyText & strTableBody & signature
.Display 'or use .Send
.ReadReceiptRequested = False
End With

'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing

 

[Minty]

That part of the code works but when I add the below code, it ignores the signature.

With OutMail

.To = Forms!Main!TxtEmailTo
.CC = Forms!Main!txtCCTo
.BCC = Forms!Main!txtBCCTo
.Subject = txtSubjectHeading
.HTMLBody = strFntNormal & BodyText & strTableBody & signature
.Display 'or use .Send
.ReadReceiptRequested = False
End With

'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing

Is that bit in red correct? Bodytext is the Outlook name for the plain text body, I thought you were using txtBody or similar ?

 

[rywello]

Yes, the bit in red is correct.

 

[Minty]

That's not what you are defining here at the beginning of your code from an earlier post;

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim [COLOR="Red"]strBodyText[/COLOR] As String
Dim OlApp As Outlook.Application
Dim ObjMail As Outlook.MailItem

So which one is it?

Or could you post up the whole code as it is now. Please use code tags to make it more legible.

 

[rywello]

Below is all the code:

Option Compare Database

Sub ReportOutlookBody()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim BodyText As String
Dim strTableBeg As String
Dim strTableBody As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strTableHeader As String
Dim strFntEnd As String
Dim lngID As Long
Dim strSQL As String
Dim mailaddress As Variant
Dim strSubject As String

Dim OutApp As Object, OutMail As Object, signature As String

'Define format for output
strTableBeg = "<br><br><table border=1 cellpadding=3 cellspacing=0>"
strTableEnd = "</table><BR><BR>"
strTableHeader = "<font size=3 face=" & Chr(34) & "Calibri" & Chr(34) & "><b>" & _
"<tr bgcolor=lightblue>" & _
TD("DCP:") & _
TD("Customer:") & _
TD("Ship-to:") & _
TD("Order Number:") & _
TD("Purchase Order:") & _
TD("Customer:") & _
TD("Product:") & _
TD("Requested Ship Date:") & _
TD("Plant Anticipated Date:") & _
"</tr></b></font>"
' StrContent = StrContent & "Limit Changes: " & Forms!Main.lstorders.Column(1) & Chr(13)
strFntNormal = "<font color=black face=" & Chr(34) & "Calibri" & Chr(34) & " size=2>"
strFntEnd = "</font>"




With Forms!Main!lstorders
For Each varItem In Forms!Main!lstorders.ItemsSelected
strList = strList & Forms!Main!lstorders.Column(0, varItem) & ","
Next
If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ")"
strSQL = "SELECT * FROM tblTrucks WHERE [ID] IN " & strList & ";"
'(send e-mail)
Else
MsgBox ("Please select an order from the list.")

End If

End With

Set rst = CurrentDb.OpenRecordset(strSQL)
strTableBody = strTableBeg & strFntNormal & strTableHeader

Do Until rst.EOF
strTableBody = strTableBody & _
"<tr>" & _
TD(rst![DCP]) & _
TD(rst![Customer]) & _
TD(rst![Ship-to]) & _
TD(rst![Sales Doc]) & _
TD(rst![PO#]) & _
TD(rst![City]) & _
TD(rst![Mat Description]) & _
TD(rst![PlGI date]) & _
TD(rst![Plant Anticipated Date]) & _
"<tr>"

BodyText = "<HTML><BODY>Hi Team,<BR><BR>Below are the Late Notifications for today. Please send out an email to the customer through the Late Order Notification Database <BR></BODY></HTML>"
rst.MoveNext
Loop

strTableBody = strTableBody & strFntEnd & strTableEnd

rst.Close


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail ' This creates a blank email and captures the users default signature.
.BodyFormat = olFormatHTML
.Display
End With

signature = OutMail.HTMLBody

With OutMail

.To = Forms!Main!TxtEmailTo
.CC = Forms!Main!txtCCTo
.BCC = Forms!Main!txtBCCTo
.Subject = Forms!Main!txtSubjectHeading
.HTMLBody = strFntNormal & BodyText & strTableBody & signature
.Display 'or use .Send
.ReadReceiptRequested = False
End With

'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing


Set rst = Nothing


End Sub


Function TD(strIn As String) As String
TD = "<TD nowrap>" & strIn & "</TD>"
End Function

 

[Minty]

I would add Option Explicit to the top of all your code modules. Add Dim signature as a string , and then change this bit to see what you get

With OutMail

.To = Forms!Main!TxtEmailTo
.CC = Forms!Main!txtCCTo
.BCC = Forms!Main!txtBCCTo
.Subject = Forms!Main!txtSubjectHeading
.HTMLBody = signature
.Display 'or use .Send
.ReadReceiptRequested = False
End With

'outlook tidy up
Set OutMail = Nothing
Set OutApp = Nothing


Set rst = Nothing


End Sub

 

[rywello]

Option explicit does not recognize the code from my other forms.

 

[rywello]

The below code works and brings back the signature when I comment out the rest of the code.

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail ' This creates a blank email and captures the users default signature.
.BodyFormat = olFormatHTML
.Display
End With

signature = OutMail.HTMLBody

 

[GinaWhipp]

You can't open it using a Form within Access, the signature will not show. You can only open directly in Outlook that is why the code posted in Post #33 works.

 

[rywello]

When I add the other code back. It flashes my signature then opens the email with the body but no signature.

 

[GinaWhipp]

Right because of the second part using...

Forms!Main!TxtEmailTo

 

[rywello]

I have taken the parts out referring to the form and it still does not bring in the signature. When I comment out the below line it works but there is no body to the email. Not sure how to get them working together.

.HTMLBody = strFntNormal & BodyText & strTableBody & signature

 

[GinaWhipp]

BodyText is Outlook Automation, so you either need a period in front or use what you declared... strBodyText

 

[rywello]

I changed the code to strBodyText and it does the same thing. It's like it opens a blank email with the signature and then the body opens over it. I have tried a few different things and it doesn't seem to want to work.

 

[GinaWhipp]

Again, you open once with the signature and then again so no signature. You need to open once and change the code as suggested.