Example below demonstrates how to:

  • Open a new email template from a saved location
  • Complete To, CC & Subject fields
  • Add Word document attachments
  • Finish with a message to the user

_______________________________________________________________
Private Declare Function OpenProcess Lib “kernel32” _
                    (ByVal dwDesiredAccess As Long,_
                     ByVal bInheritHandle As Long,_
                     ByVal dwProcessId As Long) As Long

Public WithEvents outApp As Outlook.Application

_______________________________________________________________
Public Sub CreateEmail(saveddoc_1 As String,_
              saveddoc_2 As String,_
              saveddoc_3 As String,_
              saveddoc_4 As String)

    Dim xTicketAdmin, xCC, templName, tPath As String
    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem

    Set olApp = Outlook.Application
  
    tPath = ThisWorkbook.Path
    templName = “MB Email Template.oft”
  
    xTicketAdmin = Range(“E22”).Value
  
    Select Case xTicketAdmin
        Case “Soft Ticket”: xCC = Range(“B10”).Value
        Case “Hard Ticket”: xCC = Range(“B11”).Value
        Case “Not Known”: xCC = Range(“B10”).Value & “;” & _
                              Range(“B11”).Value
        End Select
  
   Set objMail = olApp.CreateItemFromTemplate(Module1.tTemplatePath _
                      & Application.PathSeparator & templName)
  
   With objMail
       .Display
       .Subject = Module1.xSubject
       .Display
       .To = Range(“B8”).Value
       .CC = Range(“B9”).Value & “;” & xCC
       .Attachments.Add tSavePath & saveddoc_1 & “.doc”
    End With
     
    If (Module1.saveddoc_2 = “NONE”) Then
    Else
    objMail.Attachments.Add tSavePath & Module1.saveddoc_2 & “.doc”
    End If

    MsgBox “Done. Please click inside the body of your email _
          and click paste to drop in the disclaimer.”, vbOKOnly

End Sub