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
F VBA to move email from Non Default folder to Sub folders as per details given in excel file Outlook VBA and Custom Forms 3
G VBA to save selected Outlook msg with new name in selected network Windows folder Outlook VBA and Custom Forms 1
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
B Zoom automatically next email item (VBA) Outlook VBA and Custom Forms 2
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
K Outlook Office 365 VBA download attachment Outlook VBA and Custom Forms 2
A VBA Script - Print Date between first email in Category X and last email in Category Y Outlook VBA and Custom Forms 3
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
N Save selected messages VBA does not save replies and/or messages that contain : in subject Outlook VBA and Custom Forms 1
Y Filter unread emails in a search folder vba help Outlook VBA and Custom Forms 0
V vBA for searching a cell's contents in Outlook and retrieving the subject line Outlook VBA and Custom Forms 1
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
L Modifying VBA script to delay running macro Outlook VBA and Custom Forms 3
L Need help modifying a VBA script for emails stuck in Outbox Outlook VBA and Custom Forms 6
K can't get custom form to update multiple contacts using VBA Outlook VBA and Custom Forms 3
S Excel vba code to manage outlook web app Using Outlook 10
H Custom Outlook Contact Form VBA Outlook VBA and Custom Forms 1
S Problem Checking the available stores in my Inbox (Outlook VBA) Outlook VBA and Custom Forms 0
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
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 3
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

Similar threads

Top