Downloading Email Attachments And Save For Use By Macro?
Jul 2, 2013
Everyday a report is emailed to me as an attachment in .pdf and .xlsx format. It is always sent to me from the same address and comes at around the same time every day.
I already have another macro that uses that report to update our records with the new info in the report and then email our records to my boss. I would like to make it easier for other people who also get the report emailed to them to run the macro. To do so I would like to amend the macro I have to check for the email in whoever is running the macro's inbox and download only the .xlsx file and save it to the correct location.
I have tried messing with the restrict method. I want to make this thing fool proof. (Ex. if the email isn't in yet, return a msg box informing as such.)
The code for the macro I already have can be found below. Confidential stuff has been redacted.
Code:
Sub PartThree()
'This macro does the following:
'1.) runs the MTD.template.xls module 2
View 2 Replies
ADVERTISEMENT
Jul 23, 2014
I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:
Code:
If Range("B" & a) = "Y" Then
If citChev "" Then .Attachments.Add citChev
End If
If Range("C" & a) = "Y" Then
If citMits "" Then .Attachments.Add citMits
End If
If Range("D" & a) = "Y" Then
If citToyo "" Then .Attachments.Add citToyo
End If
If Range("E" & a) = "Y" Then
[code].....
Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.
Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.
View 2 Replies
View Related
Feb 2, 2007
Column A has a list of companies, column B-G has email addresses for each company (some companies have just one email, others may have four) Column H has the name of a report I would like to send to each company (It is a different file for each company)
I would like to loop through each row and send one email that includes all the contacts and the attachment.
I manipulated some code that is pretty much what I want it to do, but not quite. The below code looks like it is looking for email adresses (I don't really need that feature) and is sending an email to each address versus each company.
Sub Send_Files()
'Working in 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With.........
View 9 Replies
View Related
Apr 5, 2012
I'm having some troubles sending email as attachments from Excel using VBA where my workbooks have the XLSX name. I am getting the following message instead of the email with attachments:
FILE QUARANTINED
The original contents of this file have been replaced with this message because of its characteristics.
File name: 'winmail.dat'
Virus name: 'ScanTimeExceeded'
When I change the file extension to xls it is fine - I get the attachments on the email.
Here is my VBA:
With oLookMail
'.To = "busmgrs2@uwc.edu"
'.CC = "finspec2@uwc.edu"
.To = msToRecip
.Subject = "Accounts Status Reports as of " & msAsOfDate
.Body = "Attached are the AR Monthly Reports"
.Attachments.Add "citpfile07usoffAccounts ReceivableAR Monthly Report 2004-05.xlsx"
[Code]....
View 4 Replies
View Related
Mar 14, 2014
this macro works perfectly when I only have one attachment per email. I thought this part of the code would solve my problem however it is now not attaching any files where I want more than one attachment.
VB:
Dim files As Variant, file As Variant
files = Split(filepath, ",")
For Each file In files
.attachments.Add file
wing in the cells in column B (where I need more than one attachment):
G:DocumentsReportsAJ - 6C091, 6C0922. Feb 6C091 Cost Centre Report.xls,02. Feb 6C092 Cost Centre Report.xls
G:DocumentsReportsAJ - 6C091, 6C0922. Feb 6C091 Cost Centre Report.xls,G:DocumentsReportsAJ - 6C091, 6C0922. Feb 6C092 Cost Centre Report.xls
Full code below....
VB:
Sub Send_Emails()
i = Cells(2, "B").Value
Do ' start[code]...
View 2 Replies
View Related
Oct 25, 2012
I have a scrip that i use to safe email attachments that works and is as follow.
Code:
Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
[Code]....
Now the problem is that the email comes with 2 excel email attachments and i only want to save the one that has a name + date stamp.
View 2 Replies
View Related
Sep 3, 2012
I am using 2007. This may be something simple but I can't seem to find a solution to this, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file here is the code used for the tasks in the
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Sub step2_save_close()
[Code] ........
View 1 Replies
View Related
Sep 3, 2012
I am using 2007, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file.
Here is the code used for the tasks in the:
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
[Code]....
View 7 Replies
View Related
Aug 22, 2007
I am trying write one to search a range of cells and attach all files located in those cells to an email. I can get most of the code to work, but am having difficult getting it to search through specific cells.
View 14 Replies
View Related
Jun 12, 2014
Getting attachments from outlook subfolders.
My attachments are in Inbox > First subfolder (sales) > second subfolder(Sales 1)
I need to save attachments from second subfolder (Sales 1) to my drive.
Currently i got macro which picks up attachments from first subfolder (Sales). The macro code is
Sub SaveAttachmentsToFolder()
On Error GoTo SaveAttachmentsToFolder_err
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
[Code] ...........
View 2 Replies
View Related
Mar 2, 2009
I currently have a button then when pressed automatically sends a summary report taken from the first page of Sheet 1.
Worksheet needs to be protected all the time, but Macro only works on an unprotected worksheet.
I was wondering what additional code and where to put in so that when
protected back again after Macro has been executed?
Here’s the Macro taken from [url]
View 11 Replies
View Related
May 20, 2009
I am using the following code to send emails, it's from Ron DeBruin's site. It works, but how can I edit so that it doesn't send the file, but saves it to your drafts??
View 3 Replies
View Related
May 8, 2009
I have the code that saves it a a PDF, but a nicety would be to email it as well: -
inputfileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")
'export it - works just fine!
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=filesavename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'OK up to here!
Application.Dialogs(xlDialogSendMail).Show arg1:="", _
arg2:=EmailTitle, arg3:=Receipt
It now sends the "workbook" as an attachment and not the PDF..
How do I remove the attachment and attach the PDF Ive just created?
View 3 Replies
View Related
May 7, 2007
I am using the below code to save & email a report that is needed for a variety of different locations. I would like to be able to automate sending to multiple branches a branch specific report with some sort of a loop but I am not sure how to do it. So far, I have the below code that works for saving & emailing to one branch, however I was wondering if someone has an idea on how to loop the procedure and send to multiple branches? For example, if I have 3 branches 3 different managers:
Here is the code so far:
Private Sub emailreport_Click()
Password = "nohs1"
ActiveSheet.unprotect Password
On Error Resume Next
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb ....................
View 9 Replies
View Related
Jun 26, 2014
I have the following code in VB from somewhere. How to modify or adding from the following codes.
(1) Is anyway I can modify sedning out PDF instead of the actual workbook?
(2) Is anyway I just want the range from (A1:G80) instead of the whole sheet for printing (PDF).
View 3 Replies
View Related
Nov 6, 2009
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
Currently the code looks like this:
VB:
With OutMail
.to = sh.Range("A1").Value [code]....
View 9 Replies
View Related
Apr 15, 2014
I've been searching for a way to save a copy of a workbook as an .xlsx by bringing up the save as dialogue box so the user can name and choose the location and then to open a new email with the send as attachment function so the copy can be sent to user defined recipients.
Any similar requests that I've seen are asking for a specific file name and specific directory.
View 2 Replies
View Related
Nov 6, 2009
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
With OutMail
.to = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = sh.Range("H1").Value & " " & sarBody
.HTMLBody = strbody & "<br><br>" & "<H3><B>Specific Notes, If any...</B></H3>" & vbCrLf & varBody & Signature
.PrintOut
.send
End With
View 9 Replies
View Related
Sep 17, 2012
I'm currently using this script to download over 400 txt files which are needed for a project here at work. It works great, however, it will download the ftp files in question (which it gets a list of from another tab) in order. I would like a way to either download all 400 ato nce, or in a batch of 50 or even 100. As it is, the procedure takes around 70 mins to complete. I was hoping of a way to speed it up.
Here is the script I am using.
VB:
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
[Code] ......
View 1 Replies
View Related
Jun 5, 2009
In this workbook, I am working with several things. Once I upload my items into the listbox, put a button where I select one item and delete? What would be the code for that? When I finish with my userform, adding items, once I click order, how could I populate the cells in the spreadsheet with that info? Also, in sheet2 I have the price for those items. Once I populate the cells, how could it find the price and multiply into a total?
View 2 Replies
View Related
Jan 17, 2014
I am writing a VBA for downloading a csv file from google trend.
In the first worksheet ,enter the search keyword in the excel file and the click a button to download the csv file and place it in another worksheet in the same excel file.
I have had some experience with downloading the stock stat. csv file from yahoo but then I realize this time is really a different story.In the google trend case, I cannot find a real URL of the csv file.
I strongly believe the solution is on [URL] .....
I have tested the code in #1 .It runs but cannot return a proper csv file just like Lloyd said. I do not understand what Kyle said. What do title and id refer to ?
Besides,I do not understand and cannot run the code in #6.
What is the input and output of this code?
When I run this, I face a Run- time error '-2147024891(80070005)'.
View 2 Replies
View Related
Nov 13, 2003
rows 1 and 2 are used for my parameters.
row 1 being used for descriptions: Ticker Symbol, Start Date, End Date, and pulling in the close price and volume for each ticker symbol with the selected date range. row 2 being used for entering the parameter info.
row 2 Would read (as an example)
MSFT, 1/1/2002, 1/1/2003, close price, volume.
For each ticker in column A, add a separate wksheet named the ticker symbol and pull in the corresponding info.
So the final result for MSFT, would be an added wksheet named MSFT, with the colums headers being the close and volume, and the rows being the date parameters set in row 2.
all using yahoo finance.
View 9 Replies
View Related
Mar 17, 2013
Is it possible to access data from flash content using excel?
The data I am looking for can be found by navigating to this link: [URL] ..., selecting one of the live games by clicking on the names of the teams, then clicking on the statistics tab on the next page.
The data I'm looking to extract is the shots data, i.e. shots on target, shots off target and shots on woodwork, plus the current score.
View 4 Replies
View Related
Feb 23, 2014
I was able to create a macro that saves an email attachment based on the subject and then move it to another folder. I would like changing it to do the same thing only based one the sender’s email. I have it currently reading the save path and subject from the Excel worksheet.
[Code] .....
View 2 Replies
View Related
Jul 7, 2006
I've got a basic spreadsheet that I edit at home and at work, and I am always forgetting to either email it to myself or copy it to a flash drive. So I figure I'll use my ISPs webspace given to me, and then just edit it on the ftp site from home and work so there is just one copy. I can add the ftp site and my name and password in Excel fine. BUT when I open it, it is always read only. How do I open it with write access? I have specified my ftp username and password in Excel.
View 2 Replies
View Related
Sep 13, 2013
I am using Excel 2007 and have to send monthly payslips to respective email ids. I have the Name and Email ids in Sheet 2 Range B1:C59 , and employee codes in A1:A59. And in Sheet 1 i have the Payslip format which was automatically displayed when i select the employee code from drop down list in the cell E7 in sheet 1.
Every time i manually save the files as PDF and send to their Emails. I want the out put as whenever i select the employee code from drop down list , it should automatically save as PDF with Name of that employee ( Name of the employee is located in B1:B59 in Sheet 2 ) and attachment should automatically go to that employee's email Id.
View 2 Replies
View Related
Dec 2, 2008
I am trying to attatch an excel document to a post on this fums but it wont work.
View 5 Replies
View Related
Mar 23, 2007
Is there a way to use the function Sendmail without sending the Workbook, sheets or any attachments at all. I just would like to push a button in my Workbook so that a mail goes to the recipient saying for example: "Check our shared folder.
View 4 Replies
View Related
Jun 20, 2007
What would be disabled that is preventing me from adding attachments to my posts? I click on the paperclip and the button "Manage Attachments". Nothing happens.
View 6 Replies
View Related
Sep 6, 2012
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
View 1 Replies
View Related
