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.
 
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.
 
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
 
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.
 
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
 
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
J Outlook VBA to send from Non-default Account & Data Files Outlook VBA and Custom Forms 3
R Outlook 365 VBA AUTO SEND WITH DELAY FOR EACH EMAIL Outlook VBA and Custom Forms 0
A Change settings Send/receive VBA Outlook VBA and Custom Forms 0
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
M Outlook 365 VBA Auto-Forward Only the first of Duplicate Emails Outlook VBA and Custom Forms 2
N VBA Code Not Working correctly Outlook VBA and Custom Forms 1
L VBA to Triage Incoming Email Outlook VBA and Custom Forms 0
H using VBA to edit subject line Outlook VBA and Custom Forms 0
G Get current open draft message body from VBA Outlook VBA and Custom Forms 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
V VBA Categories unrelated to visible calendar and Visual appointment Categories Outlook VBA and Custom Forms 2
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 2
R Outlook 2019 VBA to List Meetings in Rooms Outlook VBA and Custom Forms 0
geoffnoakes Counting and/or listing fired reminders via VBA Using Outlook 1
O VBA - Regex - remove double line spacing Outlook VBA and Custom Forms 1
D.Moore Strange VBA error Outlook VBA and Custom Forms 4
B Modify VBA to create a RULE to block multiple messages Outlook VBA and Custom Forms 0
D Outlook 2021 Using vba code to delete all my spamfolders not only the default one. Outlook VBA and Custom Forms 0
K vba code to auto download email into a specific folder in local hard disk as and when any new email arrives in Inbox/subfolder Outlook VBA and Custom Forms 0
D VBA - unable to set rule condition 'on this computer only' Outlook VBA and Custom Forms 5
L Fetch, edit and forward an email with VBA outlook Outlook VBA and Custom Forms 2
BartH VBA no longer working in Outlook Outlook VBA and Custom Forms 1
W Can vba(for outlook) do these 2 things or not? Outlook VBA and Custom Forms 2
MattC Changing the font of an email with VBA Outlook VBA and Custom Forms 1
P MailItem.To Property with VBA not work Outlook VBA and Custom Forms 2
P Tweak vba so it can target another mailbox Outlook VBA and Custom Forms 1
A Outlook 2010 VBA fails to launch Outlook VBA and Custom Forms 2
richardwing Outlook 365 VBA to access "Other Actions" menu for incoming emails in outlook Outlook VBA and Custom Forms 0
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1

Similar threads

Back
Top