VBA Send Sales reports using .oft files, originate in Outlook or Excel?

[mrsadmin]

Hi there,

I am currently working on sending sales reports with a little bit more 'ease'. I have the following code set in Outlook, I am able to get 2 files, I am however wanting to get a 3rd, I don't know the string to get it however.
The problem lies in that the name needs a wildcard (I've added a note in the code).

Secondly, I want to pull some information from Excel cells themselves, month of sales, month of payment, etc. Should I switch my original VBA to excel and push the data, or can I keep it in Outlook and pull in the data?

Sub zzzAccsReceipt()
   'source:    SlipSticK:  http://bit.ly/1jzoTy7 (Template Button)
   'source:    mrexcel:  http://bit.ly/1dlG0Qr (attach with 'Date')
   '.Attachments.Add "G:\Financial Planning\" & Format(PrevDay, "yyyy") & " Daily Sales\Production\" & Format(PrevDay, "mmmm") & "\Daily Sales " & Format(PrevDay, "mmmm yyyy") & " by Channel_" & Format(PrevDay, "mmddyy") & ".pdf"
   'source:    http://bit.ly/1magjbd (strLocation) 
 
Dim newItem As Outlook.MailItem 
 
Dim dateFormat As String
     dateFormat = Format(Now, "YYYYMMDD")
      
 
Set newItem = CreateItemFromTemplate("\location\zzz accs.oft") 
 
'strLocation & the following "new items" work without any issues at all.  I can use either the strLocation OR just the location itseld 
 
strLocation = "location\zzz fees_" & Format(Now, "YYYYMMDD") & ".pdf" 
 
newItem.Attachments.Add (strLocation) 
 
newItem.Attachments.Add "E:\My Documents\" & "ehs fees_" & Format(Now, "YYYYMMDD") & ".pdf" 
 
newItem.Attachments.Add "E:\My Documents\ehs fees.pdf" 
 
[B]'This is where my issue is:  I want to be able to attach a file with a wildcard, I saved the accs files last week but only emailing them now, so how do I wildcard them?[/B] 
 
'Tried:
   'newItem.Attachments.Add "\My Documents\" & "zzz fees_" & "*"".pdf"
   'newItem.Attachments.Add "E:\My Documents\" & "zzz fees_" & """********.pdf"
   newItem.Display 
 
End Sub

Adding data from my excel sheet, not attached, only the .pdf is.

Sub EHSAccsReceipt()
   'source:    SlipSticK:  http://bit.ly/1jzoTy7 (Template Button)
   'source:    mrexcel:  http://bit.ly/1dlG0Qr (attach with 'Date')
   '.Attachments.Add "G:\Financial Planning\" & Format(PrevDay, "yyyy") & " Daily Sales\Production\" & Format(PrevDay, "mmmm") & "\Daily Sales " & Format(PrevDay, "mmmm yyyy") & " by Channel_" & Format(PrevDay, "mmddyy") & ".pdf"
   'source:    http://bit.ly/1magjbd (strLocation) 
 
Dim newItem As Outlook.MailItem 
 
Dim dateFormat As String
     dateFormat = Format(Now, "YYYYMMDD")
      
 
Set newItem = CreateItemFromTemplate("\location\zzz accs.oft") 
 
'strLocation & the following "new items" work without any issues at all.  I can use either the strLocation OR just the location itseld 
 
strLocation = "location\zzz fees_" & Format(Now, "YYYYMMDD") & ".pdf" 
 
newItem.Attachments.Add (strLocation) 
 
newItem.Attachments.Add "E:\My Documents\" & "ehs fees_" & Format(Now, "YYYYMMDD") & ".pdf" 
 
newItem.Attachments.Add "E:\My Documents\ehs fees.pdf" 
 
newitem.body [COLOR=#000000][FONT=Consolas]=[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]Item.Body[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]&[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] vbNewLine vbNewLine & 
 
[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]                Month of Sales:   <location/workbook/sheet/Cell> & vbNewLine
               Month of Payment: <location/workbook/sheet/Cell> & vbNewLine
               Payment Amount:   <location/workbook/sheet/Cell> & vbNewLine
               Payment Date:   <location/workbook/sheet/Cell> & vbNewLine 
 
[COLOR=#222222][FONT=Verdana]
   newItem.Display[/FONT][/COLOR][/FONT][/COLOR] 
 
End Sub

If I do add text it also destroys the existing formatting of the .oft that I have. I want to keep the formatting the same.

Many thanks.

 

[Forum Admin]

While its possible to get the excel data using Outlook vba, I would probably use Excel to send it over, especially if the workbook is going to be open. It may also be easier to get the third workbook, I think Excel can handle searching files better. you can't use wildcards - if the files are the only ones in a folder you can add all, otherwise you'll need to know the file name or find the file.

 

[Diane Poremsky]

Formatting is going to be difficult to retain. Where are you putting the text? You might be able to use Word's insert command. Or use bookmarks and insert it in the bookmark.

Set olInspector = Application.ActiveInspector()

Set olDocument = olInspector.WordEditor

Set olSelection = olDocument.Application.Selection

olSelection.InsertBefore strText

Set olInspector = Nothing

Set olDocument = Nothing

Set olSelection = Nothing

 

[mrsadmin]

Thanks all, I missed the .HTMLBody in one of my lines, I now have the formatting correct.

My issue is still adding the additional files. I have been able to attach 1 file with a dynamic name, but it won't bring in the rest.

'source:    http://stackoverflow.com/a/13729215/2337102
   Dim strPath As String
   Dim strFilter As String
   Dim strFile As String
   strPath = "E:\My Documents\"      'Edit to your path
   strName = "test_"  'added by me as I don't want all .pdf to attach
   strFilter = "*.pdf"
   strFile = Dir(strPath & strName & strFilter) 
 
'New email message from Template
   Set newItem = CreateItemFromTemplate("E:\My Programs\My Data\1 Outlook 2010\Outlook Resources\Templates\ehs accs.oft") 
 
'File Locations 
 
newItem.Attachments.Add "E:\My Documents\" & "test2_" & Format(Now, "YYYYMMDD") & ".pdf" 
 
newItem.Attachments.Add "E:\My Documents\testfees.pdf" 
 
newItem.Attachments.Add (strPath & strFile)

I can get the 3 files to show correctly, even the one with the wildcard (as determined in the 'str' fields), however I now can't get more than 1 to attach, there should be a total of 5 files that are attached using the dynamics above.

test_2014.pdf

test_20140214.pdf

etc

Only the first dynamic/variable/wildcard name now attaches.

Basically the template & macro will call the files that are named relative to the recipient, the whole file name will not be a wildcard, only the trailing details after _ (as above)

I hope that's clearer regarding the wildcard names.

 

[Diane Poremsky]

You need to loop to get the other files.

Try this - i didn't test it so it might need tweaked a little.

   Dim strPath As String
   Dim strFilter As String
   Dim strFile As String 'might need to use as variant
   strPath = "E:\My Documents\"      'Edit to your path
   strName = "test_"  'added by me as I don't want all .pdf to attach
   strFilter = "*.pdf"
   strFile = dir(strPath & strName & strFilter)
 
  
 
Set newItem = CreateItemFromTemplate("E:\My Programs\My Data\1 Outlook 2010\Outlook Resources\Templates\ehs accs.oft") 
 
While (strFile <> "")
     If InStr(strFile, "test") > 0 Then
       MsgBox "found " & strFile
      newItem.Attachments.Add (strPath & strFile)
        Exit Sub
     End If
    file = dir 
 
Wend

 

[mrsadmin]

Good morning

It worked, it needed a tweak, but it pulled in all the relevant tested files.

While (strFile <> "")
     If InStr(strFile, "test") > 0 Then
       MsgBox "found " & strFile
      newItem.Attachments.Add (strPath & strFile)
       End If
    strFile = Dir 
 
Wend

I removed the End Sub, and added str to "File" in the 2nd last line, and it works perfectly.

Brilliant, thank you.