If you have to create and send an email automatically, the following method might help.
I have not tested it with HTML yet, but will do that at some point.
The method requires MS Outlook to be installed, since it is automating Outlook in order to create an email with attachment.
Source code
The below method will not automatically send an email, but just display the created result. This way a user can decide, if he really wants to send an email or not allowing corrections.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-29
' @Description - Emailing provided workbook to handed email adress using provided details.
' @Param attachPath - a String providing the full path to a file you want to attach to an email.
' @Param mailAddress - a String providing the email address the mail should be send to.
' @Param mailBody - a String providing an emails body.
' @Param mailCopy - an optional email address an email should be send to in copy.
Public Function displayEmailWithAttachment( _
ByVal attachPath As String, _
ByVal mailAddress As String, _
ByVal mailSubject As String, _
ByVal mailBody As String, _
Optional ByVal mailCopy As String = "") As Boolean
Dim outApp As Object
Dim outMail As Object
Dim ret As Boolean
On Error GoTo err_handle:
If fileExists(attachPath) Then
' Reference outlook Application starting a new instance.
Set outApp = CreateObject("Outlook.Application")
' Creating a new MailItem.
Set outMail = outApp.CreateItem(0)
' Setting details of email.
With outMail
.To = mailAddress
.CC = mailCopy
.BCC = ""
.Subject = mailSubject
.Body = mailBody
.Attachments.Add attachPath
End With
' Display Email, which will be send.
' Or send an email directly using ".Send()".
Call outMail.Display
ret = True
Else
ret = False
End If
err_handle:
If Err.Number <> 0 Then
Err.Clear
End If
Set outMail = Nothing
Set outApp = Nothing
displayEmailWithAttachment = ret
End Function
If you want to send an email immediatly after creating it, just use the method "Send" instead of "Display" from the MailItem object.
Referenced API
This method uses late binding, therefore no reference of the Outlook API is needed.
However, Outlook has to be installed or the function will cause an exception and return False.
I have only tested it for Outlook 2007, but it should work for all versions from 2000.
Example
Below is an example on how to call the method displayEmailWithAttachment.
Sub testMail()
MsgBox displayEmailWithAttachment("C:\tmp\aFile.txt", "This email address is being protected from spambots. You need JavaScript enabled to view it.", "anEmailSubject", "Huhu " & vbCrLf & "Some text." & vbCrLf & "Some Greetings")
End Sub
Use the vba constant vbCrLf in order to add line breaks to an email text.
If your using HTML, it is a different thing of course but as said I did not test the above with HTML yet.
