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 RepliesEveryday 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
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] ...........
I'm using the With ActiveSheet.MailEnvelope command in vb to send an email and it works fine, but the range I am selecting includes macro buttons on the sheet.
It is possible to prevent macro buttons from been printed by selecting Format Control > Properties on the button and unticking Print Object (off by default anyway).
Is there any equivalent way of preventing macro buttons from appearing in emails when a range is emailed rather than printed.
The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?
The code is as follows:
[Code].....
I am trying to attatch an excel document to a post on this fums but it wont work.
View 5 Replies View RelatedColumn 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.........
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 RelatedWhat 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 RelatedI'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]....
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.
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]...
MY VBA code failed and sucks---
I have a Excel spreadsheet with codes, names, and emails then I have a folder that have XML files with codes that should match and be sent via Outlook 2007 as an attachment.
Example:
Excel sheet
PHA Code
First
Last
email
ALxx2
Jon
Smith
Jon Smith@xyz.com
[code]....
XML files reside in the following folder:
C:UsershomecomputerDesktopTESTxml files
ALxx2_06122012.xml
ALxy10_06122012.xml
ALzz12_06122012.xml
BTW- I have about 3,000 emails to send.
I am trying to write a VBA application within Excel to go through all Lotus Notes e-mails in a specific directory and save the attachment to a specific directory, remove the attachment and insert text into the e-mail in the spot the attachment was located that states "The attachment has been removed".
I'd also like to insert a 1KB text file into the e-mail that would be called "File Removed.txt" that would still allow you to quickly find the e-mails that had attachments because the little paperclip would show up.
How I solved my problem of sending out information to more than one email recipient using Lotus notes email and Excel 2003 vb code. I created groups in Lotus Notes for those with more than one email address Then I assigned a string to vaRecipient like so:
.SendTo = vaRecipientThe string was either one email address or a lotus notes group name.
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.SendMail "person@address", _
"This is the Subject line"
and this seems to work fine. Could there be any potential problems with this? All senders and recipients should be using outlook. Also is there a way to send the workbook but lose any macros attached. I'm not sure that all recipients will be able to receive workbooks containing macros.
I am attaching workbooks which reflect my original work. I have some questions :
1. How can I allow users to view this with everything working fine? (zipping and emailing was suggested, but I am getting some errors)
2. How can I ensure that the viewing format remains same for everyone viewing it?Al colleague of mine, tried and the formatting is wierd for him
Does any want know how to do the following:
1. In the same Workbook, I have one sheet with email addresses.
2. On Sheet 1, in Cell D3, I have the actual email address I want to send(Used data validation drop-down to get email address)
3. I´ve used many codes and it sends very well, however it does not send the email as a fully functioning workbook.
4. The recipient gets the following message "This book contains links to other sources"
I have a spreadsheet that I email daily after I update the information in it.
I was unable to find a "Code" to do so when I wrote it so what I did instead is use the Sendkeys funtion to emulate the keystrokes as if I were doing it manually. This works well with one exception.
I now have 5 email accounts and I do NOT want it to email via the default. I need to SPECIFY wich account to email thru. But I can ot figure out how to do this.
Sub Email()
' EMAIL Macro
ActiveWorkbook.Save
Application.DisplayAlerts = False
Windows("Load List.xls").Activate
Sheets("email").Visible = True
Sheets("email").Select
Range("a1").Select
SendKeys ("%f")
SendKeys ("d")
SendKeys ("m")
Application.DisplayAlerts = True
ThisWorkbook. Saved = True
When I attach a 1 page spreadsheet to an email and send it to my boss, the spreadsheet is no longer on one page when he opens it. What causes this and how can I fix it on my end or fix it from his end to keep the same format?
View 2 Replies View RelatedAdd worksheet as an attachment to an outlook email by having a button on the spreadsheet itself. However I have added another worksheet which i would like to add to the same email ie 2 attached worksheets to the same email this being decontamination certificate. I have tried to do this myself by copying and pasting some of the code and changing the paths and name but all I get is one or the other or the wrong name to the worksheet.
View 10 Replies View RelatedI found this link in numerous places on this forum to email just a single worksheet from a workbook using one macro.
HTML Code: ....
I am trying to set my cod up so I can email my excel sheet to multiple people. I tried using a ";" to separate the email addresses (as shown below) but that doesn't seem to work.
With ActiveWorkbook    .SendMail Recipients:="bubba@gmail.com;bubba2@gmail.com", Subject:=s    .Message = m    .Close SaveChanges:=False    End With
Refining the below code which repeats itself for 60 agents per worksheet, for 7 worksheets. Is there a way to loop the code so it counts up 68 cell references until the 'blank cell' condition is met?
[code]'## Agent 1
If Sheets("CSR Dashboards").Range("M3").Value = "" Then sResult = MsgBox( _
Prompt:="CSR Dashboards have been sent.", _
Buttons:=vbOKCancel)
If sResult = vbOK Then
[Code] ........
I am trying to get a macro to automatically email my workbook out to my distribution list. I have it working but I get a popup telling me:
"A program is trying to automatically send an email on your behalf.
Do you want to allow this?"
Is there anyway I can bypass this message? The code I am using is below:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "mbudgell@hotmail.com"
.CC = ""
.BCC = ""
.Subject = "NAME"
.Attachments.Add ActiveWorkbook.FullName
.HTMLBody = MyHTML & "Hi,
I have an icon in excel that i would like to attach a macro to and send sheet 2 to a specific email address.
View 4 Replies View RelatedI need to know how to send an email from Outlook using a rather large email address list (500 addresses) that resides in Excel.
Also, I need to know how to hide the individual recipients and send it out under a title name. I want all of the recipients to see the title name only - and not each other.
I am building a weekly email to my clients giving them some data on their account. When i send through file-> Share as html email, the formatting gets all screwy.
View 2 Replies View RelatedI've created a userform below and I have a code to write the data entered in the form do the following:
1. Save the data in a worksheet (Asset History) within the same workbook,
2. Copy the newly entered data into a another worksheet (Asset Form).
3. Take an image of the data from worksheet (Asset Form) and
a) Create a HTML image on out look
b) Send out an email (Get the email distribution from worksheet (EmailList)
4. I also want to be able to select the data nad change if any of the information changes using the userform.
Not sure why my code is getting stuck and getting the attached error "1004" when I get to saving and emailing. My guess is that the error is on the Checklist option explicit but I cants fix.
Here is the full coding
Private Sub cmdsave_Click()
Dim cell As Range
Dim rowoffset As Integer
[Code]..............
I put together some code in a frankenstein fashion to have a report sent to different people based on the sheet name.
I am trying to automate this so that the macro looks at each sheet name and then converts the sheet to a PDF, attaching and sending an email to the relevant person.
However I have run into a referencing issue whereby it does not pick up the data from each sheet. I'm guessing this is to do with the way I have used ActiveSheet or sh but I have not figured it and really need a fresh pair of eyes on it.
It should be looking at each sheet in turn but instead it is taking the data from the activesheet and duplicating this based on the number of worksheets in the workbook.