To send the email I use the following code to call a function:
Code:
Call SendHTMLEmail(strTo, strSubject, strBody, 1, , spath & "" & sName & "" & sFileName, spath & "" & sName & "" & sExcelFileName)
The function is:
Code:
Function SendHTMLEmail(strTo As String, strSubject As String, strBody As String, _
bEdit As Boolean, _
Optional strBCC As Variant, Optional AttachmentPath As String, Optional AttachmentPath2 As String)
'Send Email using late binding to avoid reference issues
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim i As Integer
Const olMailItem = 0
On Error GoTo ErrorMsgs
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = 1
'If Not IsMissing(strBCC) Then
' Set objOutlookRecip = .Recipients.Add(strBCC)
' objOutlookRecip.Type = 3
'End If
.BCC = "Accounting@trinitychem.com"
.Subject = strSubject
.HTMLBody = strBody
.Importance = 1 'Importance Level 0=Low,1=Normal,2=High
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
If AttachmentPath <> "" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
If AttachmentPath2 <> "" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath2)
End If
End If
End If
If bEdit Then 'Choose btw transparent/silent send and preview send
.Display
Else
.Send
End If
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message. For more information, " & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp."
Exit Function
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If
End Function