VBA & Outlook templates with attachments

[Kmahraz]

Hi:
I am looking for some assistance with my little project,
I made the file attached that will enable my team to send specific files from a specific location to a list of contact, the code work great with no issue.
Now I am trying to make the code use a template for my email, the goal is to have the code open my template email and use that for all emails sent to the list and attach the corresponding files, I was able to do so by embedding the body of the email withing my code but can't seem to do so with a template email.
Any help will be much appreciated!
Thank you,
K

 

[Stevie]

Looked here:
https://msdn.microsoft.com/en-us/library/office/aa220081(v=office.11).aspx
?

Example:

Sub OutlookTemplate()

Dim myolapp As Object
Dim myitem As Object

Set myolapp = CreateObject("Outlook.Application")
myolapp.Session.Logon

Set myitem = myolapp.CreateItemFromTemplate("C:\test.oft")
myitem.Display 'or send

End Sub

http://www.mrexcel.com/forum/excel-...template-excel-visual-basic-applications.html

 

[Kmahraz]

Thank you Stevie!
I am having challenges getting my code to work with your suggestions.
Is there any chance you can assist with this:
Current Code:

Sub EmailReport2()
Dim OutApp AsObject, OutMail AsObject, cell As Range
Dim MailBody AsString, StrPath AsString

'Use presence of a Path to determine if a mail is sent.Set Rng = Range(Range("I2"), Range("I" & Rows.Count).End(xlUp))
   ForEach cell In Rng
        Path = cell.Value & "\":    IfNot Path <> ""ThenGoTo n
   
   StrPath = cell.Value
'Get Date info from Path  Dte = Right(StrPath, Len(StrPath) - InStrRev(StrPath, "\"))
   
'Get WHOTO to check for filename (Column A)  FilNmeStr$ = cell.Offset(0, -8).Value: ClientFile$ = Dir(Path & FilNmeStr & ".*")
   IfNot Len(ClientFile$) > 0 ThenGoTo n

'Email Address  ToName$ = cell.Offset(0, -5).Value

   'Create Recipient ListFor x = 1 To 4
       If cell.Offset(0, -x).Value <> ""Then RecpList = RecpList & ";" & cell.Offset(0, -x).Value
   Next
    ccTo = Mid(RecpList, 2)
   
   'Get  Whoto code  FirstNme$ = cell.Offset(0, -7).Value:    Surname$ = cell.Offset(0, -6).Value
       
'Loop through files in Path to see if
        MailBody = "Dear " & FirstNme$ & vbNewLine & vbNewLine _
        & "Please find attached a copy of your DOP report for " & Dte _
        & vbNewLine & vbNewLine _
        & "WHOTO: " & FilNmeStr$ _
        & vbNewLine & _
       "Distributor Principal: " & FirstNme & " " & Surname _
        & vbNewLine & _
       "With thanks" & _
          Signature

       WithCreateObject("Outlook.Application").CreateItem(0)
                .Subject = "Q3 DOP feedback : "
                .To = ToName:
                .cc = ccTo:
                .bcc = cell.Offset(, 1).Text:
                .Body = MailBody
               DoWhile ClientFile <> "": .Attachments.Add (Path & ClientFile): ClientFile = Dir: Loop
                .Display
               '.Send     EndWith
        RecpList = ""
   
n: Next

EndSub

 

[Stevie]

Hi Kmahraz
it doesn't look like you've tried the suggestion.

WithCreateObject("Outlook.Application").CreateItem(0)

should be something along the lines of:

WithCreateObject("Outlook.Application").CreateItemFromTemplate("C:\test.oft")

 

[Kmahraz]

Hi Stevie,
I didn't include your suggestion in the code I posted, because I wanted to see how and what needed to change.
One quick question: for the "test.oft" is that the outlook template file i will need to create ?
I will give it a try and keep you posted.
Regards,
Karim

 

[Stevie]

Yes, that needs to be replaced with the location of your template file.

 

[Kmahraz]

Hi Stevie,
Thank you for the assistance, I was able to use what you suggested with some slight modification and got it to work.

With CreateObject("Outlook.Application").CreateItemFromTemplate("C:\Users\kmahraz\Desktop\New folder\LAUNCH  One POS data update.oft")
                .To = ToName:
                .cc = ccTo:
                .bcc = cell.Offset(, 1).Text:
Do While ClientFile <> "": .Attachments.Add (Path & ClientFile): ClientFile = Dir: Loop
                .Send

 

[Stevie]

Excellent Kmahraz, glad to hear it.
If you found any of my posts useful in this thread, please go ahead and click the like button under them.