Code needed to email Excel Workbook from cell lists via Outlook 2003

**HowardC** · 01-22-2010, 04:22 AM

Hi, I am in need of vba code to email from Excel as follows:
I have a workbook that that has TWO data validation drop down selection boxes. These have a number of names to select from. These are in cells B5 & B6.

I have the email addresses for each of these names stored in cells H9 thru' to H38.

I have a button called email. When this is pressed I will have the code email the whole workbook (which is only sheet1) to the email addresses corresponding to the two selections from the drop down boxes.

It also needs an option to send to another address stored in H30 as well. All addresses need them to all be on the same line (not cc'd in the usual cc & bcc fields).

It should prompt with a question if I want to save first. Yes or No.
With a do this or do that answer. I have code already running to handle the various saving options so if it just has the question message box I can put my code in place of the answers.

The body of the email needs to include my signature & up to maybe 300 characters of text. Can this be drawn from info in another cell & is there any limitation on the number of characters?

For my signature can the email just use my usual Outlook stored signature? I've tried a couple of simple email macros but the body of the email is just empty.

The Subject line should be the text that's in Cell B177 & the body of the email needs to be in Blue text in Tahoma font.

The email format should be html to preserve the formatting.

I am not a programmer of any sorts. Quite computer illiterate in fact but have managed to build my excel file using macro's that I've searched for & built on that. So I need code that I can just copy & paste. I assume I would add it into my modules.

I have tried copying & pasting some macros that only result in error messages yet others seem to work fine.

Is what I'm asking for doable & am I asking too much from a favour? I know that I have asked for a lot.

I'm using XP SP2 & Microsoft Office 2003.
Any help of course I'd be extremely grateful for.

Thanks in advance.
Howard Christian.

**Domski** · 01-22-2010, 06:44 AM

Hi,

Only read through this quickly. It's quite an ask for someone to put all this together for you.

You could maybe check out these links and see if you can get anywhere near what you are trying to do:

http://www.rondebruin.nl/sendmail.htm
http://www.codeforexcelandoutlook.com/

Dom

**HowardC** · 01-22-2010, 09:32 AM

Hi Dom, yes I realise this, I was thinking if I could find the right various subs I might be able to patch something together. I have found a few macros that have sort of what I've wanted but when I've tested them they often result in errors. Why some work & some don't I don't know. I will of course have a look at the links you've given me too. Thank-you. HC

**Domski** · 01-22-2010, 10:13 AM

If you get some way but are hitting problems you could always upload a sample workbook with the code you need assistance with. You're much more likely to get someone to spend a few mins fixing the code rather than a few hours building everything from scratch.

Dom

**HowardC** · 01-22-2010, 10:25 AM

Hi Dom, thanks again. I visited the links above, the first I'd already been to but the second I ended up at http://www.codeforexcelandoutlook.co...il-from-excel/
This looked a little promising but I don't understand how to run the code. They don't seem to be macro's? but Functions. I tried but couldn't make any work. Also, I couldn't understand how to address the different fields. A1, A2 etc. I'm not sure they're meant to be cells or not because I couldn't see any mention of them in the code.

HowardC.

**HowardC** · 01-23-2010, 06:20 AM

I managed to work out how to run the Function Routine called from a cell on the sheet. This script is a good way towards what I need. But I need to actually call the Function routine from another Sub.

I will have a button running the Sub which in turn will call the Function. I can't work out how to do this. Could someone advise me on this please?

This is the beginning of the Function.

Public Enum ImportanceLevel
High
Medium
Low
End Enum

Function SendMessage(Msg As String, Subject As String, EmailTo As String, _
Optional EmailCC As String, Optional EmailBCC As String, _
Optional Attachment As String, _
Optional Importance As ImportanceLevel = 1)
' fill out Outlook email message using function parameters
' by Jimmy Pena, http://www.codeforexcelandoutlook.com, October 18 2009

On Error Resume Next

Const olMailItem As Long = 0

Dim Outlook As Object ' Outlook.Application
Dim OutlookMsg As Object 'Outlook.MailItem

' create Outlook session

**royUK** · 01-23-2010, 08:24 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Also,I see no advantage in choosing to post using Blue Font

**HowardC** · 01-23-2010, 08:32 AM

Sorry I'm completely new to everything IT, I apologise & will try to put the code tags in future. The reason for blue? Well, to start with the option is there, which enabled me to post in the blue because this is our company procedure for all of our emails. It also makes it stand out & is easy to read. Is there something wrong with that? Thanks, Howard.

**teylyn** · 01-23-2010, 08:53 AM

I apologise & will try to put the code tags in future

The expectation is that you edit your post and put code tags in that post before you do anything else.

Your company procedure for emails is rather irrelevant for this forum, so kindly stick to the forum defaults unless you want to highlight a particular part of your post. That makes it easier for all forum members to read.

thanks