Is there a way to add code to the existing code that will populate the "To" area in an Outlook email directly from source information located within the worksheet being sent?
![]()
Please Login or Register to view this content.
Is there a way to add code to the existing code that will populate the "To" area in an Outlook email directly from source information located within the worksheet being sent?
![]()
Please Login or Register to view this content.
Last edited by cheddarthief; 04-15-2010 at 01:52 PM. Reason: Solved.
Hello cheddarthief,
Here is the revised macro. It uses cell "A1" as the TO address. You can change this to whichever cell you will be using.
![]()
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Leith,
Thank you so much for your help. I can't tell you how valuable this is to me. As you can see from my code below, I was able to take what you gave me and through some trial and error rewrite a few small things to better suite my needs. I have two small questions. 1.) Is there a way to skip the "Save As" screeen? 2.)You probably notice that in the ".Body" (super cool of you to add those extra items by the way) I was able to add some custom text that I always add to my email. Could I further that by basically doing what equates to (2) <returns> and then insert my name or better yet my signature? You've been super helpful already and if this is too much work, feel free to decline. Thanks again.
![]()
Please Login or Register to view this content.
Hello cheddarthief,
The "body" can be changed to include the new line characters and your signature. Is your signature plain text or do you have an Outlook signature saved?
I have an Outlook Signature that is saved. I would like to use that if possible. BTW, did you see my question about "save as"?
You have been extremely helpful.
Jim
Hello cheddarthief,
Yes, I did see that part and that is correctable as well. What is the name of the signature file you want to use?
Hello cheddarthief,
I can work with that.
Hello cheddarthief,
Finally got this finished. Way too many phone calls this afternoon. This will check for either an HTML or plain text signature file. If both exist then the HTML version will be used.
![]()
Please Login or Register to view this content.
Leith,
I copied the macro into my workbook and something is not working correctly. The new workbook is created and the file name is created correctly but at that point there is a break in the macro that prompt the error: "File not found". My thought is that the file the error is referring to is the Signature file. I say this because during my earlier macro adventures when I would try and insert my signature into the email, it was not one that was available. For some reason the only available signature was some sort of Admin that referrs back to my company. Do I need to put a copy of my signature somewhere the macro can find it?
Jim
Hello Jim,
When you create a signature file in Outlook, it is stored on your hard drive to a predetermined location. On Windows 2000 through 2007, that location is generally: C:\Documents and Settings\<user>\Application Data\Microsoft\Signatures.
If your signature file is located elsewhere then the macro will need to know that new location. Also, if you are using VIsta, the default folder location is different than above.
Leith,
My signature is located there but something is still not working. Actually, it doesn't need to go to my physical signature saved somewhere since that seems to be causing a problem. If the body of the email could just say "Please see attached RFQ." then a [return] and my name "James J. Bender" that would be fine. But there is one more issue. When I copy the first worksheet and make a change to cell (C18) then try to email that sheet, the macro is still picking up the information in the original sheet and not the new one. For example:
The first sheet has the folowing values: C17=PENNDOT 29919 and C18=5056-0100. So the created new file per the macro is called RFQ-PENNDOT 29919-Ref5056-0100.xls
The second sheet has the following values: C17=PENNDOT 29919 and C18=5020-0002. So the created new file per the macro should be called RFQ-PENNDOT 29919-Ref5020-0002.xls but instead it's giving it the same file name as the first worksheet.
Not sure what's happening there. Any ideas?
Jim Bender
Hello Jim,'
I need to change the macro to work with the ActiveSheet and change the signature portion of the code as well. I have to go out for a while but will return in about an hour.
Hello Jim,
Got back and took a look at the signature file problem. Th Dir function will only find the signature file if it is a literal string (full file path in double quotes). It will not find the file if it is in a string variable or in a variant variable. I overcame this problem by using the Shell.Application object to retrieve the file. So, long story short everything should be working 100%. Here is the updated macro...
![]()
Please Login or Register to view this content.
Last edited by Leith Ross; 04-09-2010 at 04:27 PM.
I wish I could say that worked, or event that I knew what it all meant but now I'm getting an error in a Microsoft Visual Basic box. The error reads: "Compile error: Invalid Outside Procedure"
The macro looked good it's just not working. Should I attach a copy of the actual Excel file to the link?
Jim
Hello Jim,
That would help.
I added the file. It is attached to the very first quote. Usually there would be a lot of tabs but I removed all but a few so the file size is smaller. Hopefully this will help.
Jim
Hello Jim,
I reinstalled the macro and it works on my end. The attached workbook should work fine. Let me know what happens.
Leith,
That worked great as long as I run it in the file you sent back. It will not run when I copy it into existing file. I just cut and copied the updated macro into an existing file that used to have the old macro. For some reason, the new macro will not select the information on the current tab, just the information on the first tab. This is what the old macro used to do before you fixed it. I thought maybe the macro editor was not overwriting the old macro so I completely deleted the old macro, saved the file, opened it back up, then installed the new macro. Same thing. It will only email to the original tab, regardless of which active tab I'm in. This is so frustrating. I will just attach my original "RFQ" file instead of the trimmed down one. The password is "abi" all in lowercase. If you could somehow get the code in the file, resaved and sent back to me, that would be great. I'm not sure what I'll do on older files, but at least this would work moving forward. Good luck.
One last question: how do I in the body of the email show (2) returns then my name? I'd like the body of the email to read:
"Please see attached RFQ.
Jim Bender"
The current code for the body is as follows:
![]()
Please Login or Register to view this content.
Thanks again.
Jim
Last edited by cheddarthief; 04-12-2010 at 09:41 AM. Reason: additional information plus adding actual file
Hello Jim,
Here is the revised macro. This will email all the visible worksheets. I also added some code to prevent an email from being sent if cell "C16" is empty or contains a formula error. As long as you have a signature file named "James J. Bender" that is either plain text or HTML, the macro will add it automatically to the email. Now make break is over and I have to get back to finishing my taxes - what fun! The new macro has been added to the attached workbook.
![]()
Please Login or Register to view this content.
Now this is just a slick piece of writing there!!! That is something I never thought to ask for, not because I never thought of it but more that I thought it would be way to difficult to pull off. I took the file you created and did a test with it. Since there are 6 tabs, it should create 6 email requests to the appropriate person as per the macro. And it did just that. However, it keeps creating a 7th email that is an error that I have to delete. A small price to pay for such a powerful macro but odd that it does that. I looked through the code and could not see anything that would make the 7th email. I'll keep looking but thank you so much for everything.
Jim
Hello Jim,
Sorry about that. I had an "If...Then" checking to late. This version is corrected. Replace the old macro code with this code.
![]()
Please Login or Register to view this content.
That eliminates the extra email but there are still two problems. 1. If a worksheet has no value in cell C16, the macro erros out. 2. And this is a biggie, all 6 recipients are getting a copy of the same worksheet, the first worksheet, rather than the one for that particular company. I put "ABC" and "123" in the project and ref cells respectively then picked a vendor in the validation drop down for each of the worksheets and tried to run the macro. When looking at each email, the actual attached Excel file had the same information in all of them. Sorry.
Jim
Hello Jim,
I should not try to write programs and do taxes at the same time. I corrected the oversights. One was for the C16 test. I forgot the "VarType" construct which tells me if the cell is empty or there if there is a formula error. I have run the code and it works correctly. Here is the corrected code. If you find any more problems, let me know.
![]()
Please Login or Register to view this content.
Last edited by Leith Ross; 04-13-2010 at 12:33 PM. Reason: Tested the code
Did you test this in the actual Excel file? It's still using the first worksheet information for ALL email recipients. Just to confirm, go to the first sheet "QUOTE-MILL-STRUCTURAL" and delete the company name from the drop down list. This should clear out all the other information except for the project and ref. Now run the macro and it should generate only enough emails to match the number of worksheets you have emails populated in the C16 cell. Then, go to each email and open the actual file that was attached to the email. All of the emails will not have information in the company profile area because you don't have any in the first worksheet. If that DOESN'T happen, then I've done something wrong on my side so please just upload the actual Excel file and I'll run it here again. Thanks.
The good news is that you took care of the empty cell variant you referred to in your previous update.
Jim
Hello Jim,
Here is the file I used for the test. It is your file with all the email addresses on the Contacts worksheet set to my own for testing. This includes the macro.
![]()
Please Login or Register to view this content.
Leith,
Here is what I see that is happening. You can confirm on your side. The macro is in fact creating the file name correctly for each email, attaching it to the corresponding emails and filling the body of the email as requested. The problem is that the content of those files is the same for every email. Which ever worksheet is the "active" worksheet at the time the macro is run happens to be the information in all the email attachments. If I had one worksheet and needed to send that same sheet to 50 people that would be great. But I have individual worksheets all with different information that need to go to their corresponding individuals noted in cell C16 of each worksheet. I hope that is clear to understand as I'm not always so good at explaining things when I don't possess the vocabulary needed. Also, it appears as though for the first time the workbooks created for the emails now have the actual macro attached to them. This didn't happen before so I'm not sure what changed in the code. If I try to send an email with an Excel worksheet that actually has a macro attached to it, most company firewalls will block it worrying about viruses.
Jim
Last edited by cheddarthief; 04-14-2010 at 11:53 AM. Reason: correction to how the macro is working
Hello Jim,
The problem seems to be the Formulas. When the sheet is copied all the formulas and their references are copied to the new worksheet in the new workbook. I need to make some adjustments to the macro to prevent this. The emailed version will be a "picture" of the actual quote. This should remove the problems.
Leith,
Are you referring to an embedded "picture" of the actual quote in the body of the email vs an attachment? If so, I can't do that. My vendors need the form in Excel format so that they can fill in the values requested and return.
Jim
Hello Jim,
To make the emails interactive, you will need to create an individual workbook for each quote that has all the other hidden sheets it needs and no macros. Another "master" workbook would need to created to allow you to select the quote workbook you want email. The master would contain the macro needed to email the open quote workbook.
Writing code to remove unneeded sheets and macro modules before emailing the quote can be done but is demanding in time, skill and knowledge. Because of this, I would not take on such project for free. The approach above would the quickest and easiest to accomplish your goal.
I completely understand as your time I'm sure is very valuable. I will just have to go back to physically clicking on each individual worksheet, running the macro for that particular sheet and sending. Then go to the next worksheet and so on. This is still faster than my previous way. Are you able to remove the feature that emails ALL worksheets at once? I'm okay with going to each individual worksheet and running the macro individually. I just need the macro to name the file as is indicated in the current macro to the information ONLY in the current open worksheet.
I really can't thank you enough for all your help.
Jim
Last edited by cheddarthief; 04-14-2010 at 03:08 PM. Reason: additional information
Leith,
Don't worry about making any changes. I was able to "trim some of the fat" out of the code to get it to do the bare minimum. I will just have to open each worksheet and run the macro for each sheet but at least I won't have to save it, open Outlook, attach it, fill in the subject line, etc. each time. Thank you so much for your help. You have been awesome.
Jim
P.S. I attached my final code in case you're curious.
![]()
Please Login or Register to view this content.
Hello Jim,
You will still have problems sending only one quote at a time. The data validation drop downs on the sheet are linked by back to your workbook that creates the quote. The user will not be able to utilize these for making selections. If the user responds with "Yes" to updating the links after the file is downloaded, there will be problems.
The macro here successfully copies each of the quote sheets, creates an new workbook and emails it. While there are no macros in these workbooks, the link problem still remains. This is why I made the suggestion about creating individual workbooks for each quote.
Macro to Attach Each Quote as a Workbook and Email it with a Signature
![]()
Please Login or Register to view this content.
Leith,
I see what you're saying about the link issue. However, I'm not sure it's really a problem. I really don't want/need the vendor to access the dropdown menus for this purpose. The only part they have to do is fill in their quoted costs in column G under the header "Unit Cost". They won't be changing any of the variable data that I use. When I've done a test and opened the file both ways (update and don't update), the result has been the same. The values in the cells remain; they just can't access the drop down information which is okay. The only real reason for them needing to open this file as an actual Excel file is they can input their prices and either return it or they print it out, write in their prices and fax it back. I just wanted to cut down on all the faxing and paper waste that my company has been accustomed to doing over the years.
Jim
Hello Jim,
In that case, this macro will remove the links from the worksheet/ The user will be able to fill in the worksheet and email it back to you when done. Her is the udated macro and workbook.
![]()
Please Login or Register to view this content.
Leith,
That is awesome. That's basically everything I need. I like that fact that if there is not a recipient, it just skips that page and does not error out. So, since this one does ALL visible worksheets within the workbook, which lines would I remove from the code to email only the active worksheet? I'm asking because there are times when I have to send a revision to a request and only want to send that sheet, not all of them.
Jim
Hello Jim,
Here is the code to email only the ActiveSheet as the attachment.
![]()
Please Login or Register to view this content.
That's it!!! I've tested both and they both rock! Thanks so much for your help. I could have read a book on writing these macros and still spent the next 2 years trying to figure out how to write these and then still not have done it correctly. Thanks so much for your help. I really do appreciate the help. This will make my job so much more efficient. I can't wait to share the new template with my co-workers. You are truely an Excel Guru.
Thanks again,
Jim
Hello Jim,
Glad you like the results. Makes "tax day" much better for me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks