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
1 comment
Comments feed for this article
October 17, 2007 at 1:46 am
TJ
Could you provide a little more explanation for those fairly new to VBA in Outlook? I’ve used VBA extensively in Excel but Outlook is new to me. I have been searching for something that allows for easier sending of template responses with attachments, similar to what this example seems to be doing. Could you help me understand the areas that I’d want to change, those that are hard-coded in this example, to personalize the code for my purposes?