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.

1
2
3
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.