Yasser asked: How can I add signature parts? ...point me the name of the procedure and the line at which I need to add or edit to be able to follow changes
In post #6 See Sub SendEmailViaOutlook() in code Module ModOutlookEmail .
The best way to answer is through an annotated example. First we will start with a relatively simple working code example. To add to the simplicity, Outlook MUST be open before the code is run from Excel.
The example does the following:
a. Uses the Default Outlook Account as the sender
b. Uses the default Outlook Signature
c. Uses a simple HTML text message
Option Explicit
Sub SimpleOutlookEmailDiplayWithTextBodyAndSignature()
'This assumes that Outlook is already open to simplify the code
'Reference: http://www.w3schools.com/html/html_paragraphs.asp
'Reference: http://www.html.am/html-codes/text/
Dim OutApp As Object
Dim OutMail As Object
Dim sHtmlBody As String
Dim sSignature As String
'Attempt to create an Outlook object
On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Err.Clear
msgbox "NOTHING DONE. The Outlook Object could not be created from Excel." & vbCrLf & _
"Try again when Outlook is open."
Exit Sub
End If
On Error GoTo 0
'Create the Body of the Email
sHtmlBody = _
"This is line one of the email body." & "<BR>" & _
"This is line two." & _
"This is a continuation of line two."
'Create the Outlook Mail Object (using the default Email account)
Set OutMail = OutApp.CreateItem(0)
'Display the Default Signature (If Any) - it is contained in .HTMLBody
'If there is no default signature, nothing is displayed
OutMail.display
sSignature = OutMail.htmlbody
'Determine the values to be sent
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Info Request"
.htmlbody = sHtmlBody & sSignature
.display
'.Send - comment out the 'Display line' if you want to send
End With
'Clear the Object Pointers
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Now to describe what various parts of the code are used for.
This opens Outlook only if it is already open using 'Late Binding'. For examples of 'Early Binding' vs 'Late Binding' see:http://www.excelforum.com/showthread.php?t=1020212
Early Binding is Excel version dependent and requires use of a VBA Library reference. If moving code between computers that use different versions of Office, the other computer must explicitly open the 'Library Reference' on the new computer.
Late Binding is version independent. However, if Outlook Constants are used, their values must be explicitly used.
For Outlook Constants and their values see: https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
Set OutApp = GetObject(, "Outlook.Application")
The Email body is saved in a separate string variable and can go anywhere in the code prior to the .Body or .HTMLBody statement that uses the variable. When using a Signature that contains something other than straight text, the Email body must use the HTML "<br>" as an EOL (End of Line) indicator. "<br>" is roughly equivalent to vbCrLf (Carriage Return - Linefeed) or vbLf (Linefeed) depending on the EOL convention of the computer. If the Body and Signature combination use Text only, the .Body statement is used and I use vbCrLf as the EOL character.
Dim sBody as string
sBody = _
"This is line one of the email body." & vbCrLf & _
"This is line two." & _
"This is a continuation of line two."
If the Body or Signature contains HTML (and/or picture in the Signature) , the .HTMLBody statement is used and "<BR>" must be used as the EOL character.
Dim sHtmlBody As String
sHtmlBody = _
"This is line one of the email body." & "<BR>" & _
"This is line two." & _
"This is a continuation of line two."
After the 'Outlook Application' object is created, a 'Mail Object' must be created. The 'Mail Object' references a specific account which is usually Account '0' (the Default Account). If Outlook has 5 different Email accounts, then the Email address with Account 4 can be used as shown below.
'Create the Outlook Mail Object (using the default Email account)
Set OutMail = OutApp.CreateItem(0)
'or
'Create the Outlook Mail Object (using the Email address associated with Account '4')
Set OutMail = OutApp.CreateItem(4)
The 'Mail Object' does the rest of the work. There is no specific VBA command to get a signature, so a little trickery is required. If the Email body is BLANK the .Display command will display the Signature associated with the 'Mail Object' (a specific Email address). The signature can then be saved for later use.
Dim sSignature As String
'Display the Default Signature (If Any) - it is contained in .HTMLBody
'If there is no default signature, nothing is displayed
OutMail.display
sSignature = OutMail.htmlbody
Most of the following items are self-explanatory:
'Determine the values to be sent
With OutMail
.To = "a@b.com;c@d.com"
.CC = "" 'Carbon Copy
.BCC = "" 'Blind Carbon Copy (other recipients don't know this person sees the Email)
.Subject = "Info Request"
'...
End With
Combine the Email Body and the Signature
With OutMail
'...
.htmlbody = sHtmlBody & sSignature
'...
End With
Add attachements if any
With OutMail
'...
.Attachments.Add "H:\abc.docx"
.Attachments.Add "H:\def.docx"
'...
End With
Finally,
a. To Display the Email in Outlook for Editing and review in Outlook:
With OutMail
'...
.Display
'...
End With
or
a. To Send the Email to Outlook in the 'Send Queue'. This command DOES NOT send the message. Outlook is usually configured to Send/Receive periodically or to Send/Receive on Manual Commands by the User. The Email will be ACTUALLY SENT according to the Outlook setup.
With OutMail
'...
.Send
'...
End With
To send the Email NOW is a topic for a completely different thread at some future time.
Lewis
LinkBack URL
About LinkBacks
Register To Reply
.
Bookmarks