Excel / VBA / Outlook Integration - Attach Most Recent File
Jun 21, 2013
I am trying to create a script for a manually morning process that I need to complete that entials downloading a number of .PDF files and sending them out to a distribution list. I am unable to automate that actual saving of the files onto the network drive, but I believe I can automate the process of attaching them to the email. I have run across some problems so far though, this is what I have been able to come up with. After doing a bit of research it seems as though using cmd would be the best way to locate and attach the file but I am still unable to get it to run properly:
.To = "email@example.com"
.Subject = "Test"
.Attachments.Add Split(CreateObject("wscript.shell").exec("cmd /c Dir 'G:Client ReportingMGIDaily
ReportsAGFAJune 2013*.pdf' /b /o-d").stdout.readall, vbCrLf)(0)
Here is an example of a folder I will be trying to pull the most recent saved PDF from, there are about 10 similar folders:
I feel like I am relatively close to completely this, my cmd syntax may just be a little off.
I'm in search of a VBA macro which will send a mail through outlook automatically with the count of files(.pdf + .msg files) in any given folder.
I work for an organization, where we use Outlook for mailing purpose.
Everyday Inventory(.pdf files and .msg files) will be saved in a folder that is to be processed.
I want a macro which should automatically send the total inventory (.pdf files + .msg files) through my outlook mailbox(say: firstname.lastname@example.org) to a my superiors outlook mailbox(say: email@example.com).
If the macro sends that email to my superior before me and my superior logon to computers at perticular time (say: 7:00AM daily), need to plan our day at work.
I am looking for a macro that will convert the active sheet in an Excel 2010 file to a PDF, attach the PDF to an Outlook 2010 email message, and fill in the subject line and recipient address which are the same each time.
I have a sheet with an invoice on and wish the Macros to make it easy for users.
I have this code which saves as PDF, Prints and saves my worksheet. However I want to send the PDF to Outlook with To: and cc: completed using the Macro.
Sub PlasticParts() 'Saves active worksheet as pdf using concatenation 'of A1,A2,A3 Dim fName As String With ActiveSheet fName = .Range("A1").Value & .Range("F2").Value & .Range("A3").Value .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ " [Code] ..........
I'm generating a letter (Word doc) and populating bookmarks with data on a worksheet using the following code which is assigned to a Button and it works a dream (in terms of opening the Doc and populating the bookmarks with whatever data is in the relevant cells on the worksheet):
Sub test() Dim objWord As Object Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Set objWord = CreateObject("Word.Application") objWord.Visible = True objWord.Documents.Open "location of the letter.doc"
However, what I now need to do is Create an Outlook Item (email) with a Standard Subject and send to an email address that will be on the same worksheet (in cell M2 for instance) and with a standard Body.
I dont really need to open the Word doc, like it does at present, but I do need the Bookmarks contained within it to be populated with the source data contained within the Worksheet and subsequently have it attached to the email fully populated - maybe I need to 'close' / 'save as & close' the Doc post generation?
I am using a macro to pull and arrange data from two worksheets into a "master" sheet. After I run the macro, I have a large set of data but no way to see which sheet each entry came from.
I would like to have a MATCH formula run in a column to the left of the data. Column A will indicate if the data line is present in the sheet A, and Column B will indicate if the data line is present in sheet B. In the past, I have used something like the following:
I currently have a spreadsheet setup on a Macro to send & distribute a message from an 2007 Excel file to multiple users through Microsoft Outlook 2007. It is currently setup to send as an attachment. I need to find a way to have this file be sent as a PDF file. I know you can save Excel as PDF's so there must be a way to send them & attach them to as a PDF.
When I click on the "Office Button", then in the recent "documents dialog box" there is a list of my worksheets plus an "allsenatename" file". This then downloads a worksheet from a website. I don't know how it was added to my list, I must have entered it when looking at that web site in error.
I have 3 routines to (1) create an empty zip folder, (2) place a file into the zip folder and (3) create a mail item and attach the zip folder to it.
The zip folder is successfully created. The mail item is also created and displayed, yet the zip file does not get attached.
Sub NewZipFile(strFilePath As String) If Len(Dir(strFilePath)) > 0 Then Kill strFilePath Open strFilePath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub
Sub MakeZipFile(strPath As String)
Dim strFileName As String Dim objApp As Object
With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False.......................
I have developed a product that's a bunch of Excel spreadsheets strung together. Real estate and mortgage professionals use the tools to counsel home buyers. We include a pdf converter in case recipients of emailed file attachments don't have Excel.
We provide an 'Export' procedure that calls a custom dll that uses the outgoing mail server that the user [must] setup or our web's mail server as a default. The action attaches the Excel or the PDF file and sends in the background. We use an Excel form for user to write the message and the process saves a copy of the message sent and the file send in an Archives folder.
This procedure is not very user friendly, as it does not offer the user's address book to select a recipient, does not put the message in the user's email client Sent folder...Plus the Archives folder could become quite large...plus it involves a user setup that creates alot of support calls.
But actually the most critical shortcoming has become that ISPs (Comcast and ATT so far) are blocking the use of custom email clients!
Recently my son showed me in QuickBooks their attach file-to-email process. It simply opens the user's default email client with the file attached. I need an app that does that! And it needs to be distributable.
I have written a macro but unable to make it work. The macro works but not the way I would like it to.
The macro has to save an excel sheet to a separate folder as a pdf format, a message box should pop up confirming the pdf file and then attach the same saved pdf file to an email.
Now the macro saves the pdf file where I need it to be saved, the msg box pop up and then the pdf file opens up on top of the msg box. If I close the pdf file the msg box which was hidden under the file is now shown. When I click "OK" it goes straight to outlook with the email addresses attached but the saved pdf file is not attached.
I am using a macro to e-mail any work sheet with an address in A1. I would also like to attach a file that will be listed in cell E1 of that worksheet. The file is different for each work sheet that is being sent but will be listed in the same cell of each work sheet.
This is the code I am using (i got it from ron de bruin) when I use the .Attachments.Add (C:est1.txt) it works but i can not seem to figure out how to get it to read the file address in the E1 cell. The code i am trying to use is .Attachments.Add = ws.Range("E1").value.
I've got this code that will copy data into my worksheet from the most recent .IRP file generated on my C:/ drive. Along with copying the data to my worksheet, I'd like to have a copy of the IRP file copied to a folder on my L:/Drive.
In the code section I've commented out the code that allows the data from the most recent file to be copied to my worksheet. I found code that supposedly will copy a file to a new directory. I want to combine this into my code to allow both to happen. Here's my attempt.
I am using Excel 2007 so I CANNOT use application.filesearch.
With that said, I need to change one of my old macros that searched for the newest file within a specified folder, and then display the name of that file in a message box that gives the person an option of opening the file. I've read too many workarounds for application.filesearch and I'm not sure how to combine them all into something I can use.
Normally I have a bit of code to start with, but this one has me so baffled I don't know where to start.
I have this Macro working almost perfectly, but I need to attach the worksheets to the emails. The code is below. I am unable to reference the ".Attachments" to the workbook created previously in the code.
Sub ActivityReport() ' Clear out any old data on Report Sheets("Report").Select
Is it possible to set the default page to open when clicking the file button? In particular, I use the recents feature a lot but never have I looked at the information on the information page. Everytime I click the file button I have to then select recents which is annoying.
show me the visual basic codes that allow me to attach the current saved workbook to an email (just attach, but not to send the file). So basically, just open the Outlook, send to, and attach the file.