You are here:
- Home
- Computing/Technology
- Microsoft Software
- Excel
- Tweak to macro that sends attachment
Excel/Tweak to macro that sends attachment
Advertisement
Question
Bill
Your macro below to send excel file as attachment:
What would need to be added to pull the email recipients
email address from a separate excel spreadsheet that would
reference a certain cell within that spreadsheet.
don
Here is some code from the web to send an email in Excel without Outlooks Security prompt appearing asking if this is safe to send.
Sub Mailer()
Sheets("BB Email Data").Select
pathname = Range("A1").Value 'defines attachment
dname = Range("B1").Value 'defines date for subject
Dim objol As New outlook.Application
Dim objmail As MailItem
Set objol = New outlook.Application
Set objmail = objol.createitem(olmailitem)
With objmail
.To = "whoever" 'enter in here the email address
.cc = "whoever" 'enter in here the email address
.Subject = "Daily test email for " & dname
.Body = "Please find attached the teste email" & _
vbCrLf & "If you have any queries can you please let me know" & vbCrLf
.NoAging = True
.Attachments.Add pathname 'adds attachment to email
.display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True 'send the email without prompts
End Sub
Answer
Don't remember writing this one but regardless I don't follow fully. Is the list in another wb? If so, are there formulas in the current wb that contains the code that references those cell so that the current wb "shows" the names?
If the names are just in another wb then you will have to open that one and grab the names first. Then just string those names together in a variable string and use that variable in the .To line
Related Articles
- How to Send an Email with Any From: Address in Outlook - About Email
- How to Automatically Cc: All Mail You Send in Outlook - About Email
- Sending Email Messages with Attachments using Delphi and Indy
- Send Personally - Outlook Bulk Email Add-On Review - About Email
- How Worms Spread via Email (and How to Avoid That) - Part 1: Introduction
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Answers by Expert:
- Nathan Head
- Chad Welch
- Philip Buckley
- Tushar Sakhalkar
- Mark Kaphle
- Amal
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bob Umlas
- Isaac
- Edward H. Mori
- Jessica L.
- Jan Karel Pieterse
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- Craig
- Sixthsense
- Adelaide carvalho
- Miguel Zapico
- JP
- Douglas M. Smith
- Richard Rost
- Victor Lan
- Bill
- Anne Troy
- Claire Blinman
Bill
Expertise
I can provide help with most all EXCEL questions and most all questions about writing EXCEL macros. I have been developing macros for about 10 years in EXCEL and have switched to it from Lotus 1-2-3 after about 10 years of writing macros in it. Typically, I will not write a macro for you unless it is very short because of all the details a macro has to know about to work every time all the time. Please understand that I do not know it all and will be the first to say so. As politely as possible, I don't write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details to have the code work the first time and every time. This typically means too many follow-up emails, and subsequent macro changes due to lack of specific details, just to get those details so that the macro would work, all of which is on my own free time. The voice of experience from responding to many questions from people who ask me to write a macro for them from this site tells me this. I don't mean to come across as unhelpful but macros are usually very specific and without ALL of the specifics the macro I would write will not address all of your needs and the layout, location, formatting, conditions, etc. of your data and any related files the macro would have to work with. What seems like a simple task to you is almost always more involved than what you think to have the macro ALWAYS work in EVERY situation. If you have a macro you have already written and have a question about it then perhaps I could help with that. I am sure and hope you can and do understand.
©2010 About.com, a part of The New York Times Company. All rights reserved.