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

Status
Not open for further replies.
M

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?

Code:
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.

Code:
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

Senior Member
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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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
 
M

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.

Code:
'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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You need to loop to get the other files.

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

Code:
   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
 
M

mrsadmin

Good morning :)

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

Code:
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
diver864 vba for a rule to automatically accept meeting requests with 'vacation' in subject, change to all-day event, change to free, don't send reply Outlook VBA and Custom Forms 1
PGSystemTester VBA To Change AppointmentItem.BusyStatus From MeetingItem Before Send Using Outlook 0
A VBA macro for 15 second loop in send and received just for 1 specific mailbox Outlook VBA and Custom Forms 1
S Change VBA script to send HTML email instead of text Outlook VBA and Custom Forms 3
M VBA to send reminder email if no response Using Outlook 13
D Using a VBA Custom Form to Send Reoccurring Email Upon Task Completion Outlook VBA and Custom Forms 4
stephen li VBA Outlook send mail automatically by specified outlook mail box Outlook VBA and Custom Forms 1
B VBA Help Email that will save as draft and send as attachment Outlook VBA and Custom Forms 3
K VBA to prompt and send a CC Outlook VBA and Custom Forms 6
N VBA Script to Send Automatic Emails from Outlook 2010 Outlook VBA and Custom Forms 1
D Outlook VBA to open Excel attachment and send recipient's email address to a workbook cell? Using Outlook 4
H Problems With Outlook 2013 VBA To Send and Print an email Outlook VBA and Custom Forms 1
D VBA Script (Ask to where to save send mail) Outlook VBA and Custom Forms 1
V "Accept + Send the Response now", VBA script? Using Outlook 1
D VBA: Send-From Code for Template Shortcut? Using Outlook 0
D Selection of Send-From Account with Template VBA Shortcut Using Outlook 0
L Send E-mail with VBA code from [E-mail Distribution Group] if I have “Send as” Using Outlook 6
S Outllok 2007 VBA code to send mail automatically from drafts folder Using Outlook 1
S Send All emails in Outbox and Quit using VBA Outlook VBA and Custom Forms 3
D Using VBA to send Word document as body of message works in 2007, but not in 2003 Outlook VBA and Custom Forms 9
S Add VBA save code Using Outlook 0
C Auto Run VBA Code on new email Outlook VBA and Custom Forms 1
O VBA Cases with Listbox - Can you use Multi-Select? Outlook VBA and Custom Forms 4
O VBA Outlook Message Attachment - Array Index Out of Bounds Outlook VBA and Custom Forms 0
V Modifying the built in forms with VBA Outlook VBA and Custom Forms 4
S Excel VBA and shared calendar issue Outlook VBA and Custom Forms 4
L Macro/VBA to Reply All, with the original attachments Outlook VBA and Custom Forms 2
L VBA unknown character Outlook VBA and Custom Forms 2
G Move tasks up/down todo list by VBA Outlook VBA and Custom Forms 1
K Use VBA to find Sender and Recipient from Microsfot 365 Journaled Email Items Outlook VBA and Custom Forms 3
J Want to learn VBA Macros for Outlook. What book can you recommend? Outlook VBA and Custom Forms 2
F VBA code to dock Styles whenever I write or edit an email Outlook VBA and Custom Forms 0
C VBA to prompt for Sent folder destination Outlook VBA and Custom Forms 3
B Adding signature to bottom of VBA reply email Outlook VBA and Custom Forms 1
B Change Font and Font size using VBA Outlook VBA and Custom Forms 9
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
D.Moore VBA script fail after Office 365 update Using Outlook 8
R Limiting length of saved attachment in VBA Outlook VBA and Custom Forms 2
S Skype for business meeting vba code Outlook VBA and Custom Forms 1
C How to use VBA to show only items x days old or more Outlook VBA and Custom Forms 1
B VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
S Reference Custom Fields with VBA Outlook VBA and Custom Forms 2
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
G VBA Macro Calendar Printing Assistant 4
R Help Revising VBA macro to delete email over different time span Outlook VBA and Custom Forms 0
B VBA to Collapse Task Folder Groups Outlook VBA and Custom Forms 1
R Expand VBA Permanent Delete Code Outlook VBA and Custom Forms 6
shrydvd vba to secure zip attachments Outlook VBA and Custom Forms 3
M Adding Subject to this Link-Saving VBA Outlook VBA and Custom Forms 5

Similar threads

Top