E-mailing excel cells in body of e-mail

[bturner2]

I was wondering if there is any way to e-mail the cells and its contents of an excel sheet using vb. I would like it to be sent in the body of the e-mail rather than an attachment and I would like to keep the cells so that the information can be copied and pasted by the recipient of the e-mail without it copying the entire text of the e-mail. Was just wondering if it could be done.

I also need some help with checking information on several sheets in a workbook. I have a workbook with the following sheets. Sheet1 is named duh. The second sheet is named big_duh. The third sheet is named biggest_duh. Now what I would like to do is click a button and have it check cell a1 to see if the number is greater than 50. If it is above 50 do something if it is not then I want it to go to the next sheet and do the same thing until it has gone through all the sheets. Now there will be sheets added to this at a latter time and the number of pages and names will be changing all the time.

How can I get it to recognize the number of sheets, the names of each sheet and then advance to the next sheet if I don't have any idea how many sheets there will be or what the sheets may be named?

[Zack Barresse]

For your first question, Ivan F. Moala has that information right here...

http://xcelfiles.homestead.com/VB_Quick17.html

As for your second question, this will do ya:

Sub duhChecker()
    Dim ws As Worksheet, sht As String
    For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    If [A1].Value = "50" Then GoTo nextSht1
    'other code here
    Next ws
    MsgBox "Nope"
    Exit Sub
nextSht1:
    sht = ActiveSheet.Name
    MsgBox sht
    'do stuff if 50 is found
End Sub

This will work for whatever your sheet name is, and however many sheets you have. Just take out the msgbox sht part and replace the 'do stuff if 50 is found with whatever code you want. The msgbox was for testing purposes only.

The big caveat with this code is (and I'm not sure how you want to handle it) is if there is 50 in multiple sheets, this code will stop at the first value found. So if there is 50 in A1 of more than one sheet, and you want to do something for those, this will need to change.

[Daniel Klann]

Hi,

EDITED : Zack, I think the code you referred to was for saving a range as a GIF, not for e-mailing.....

There is some code on my site : http://www.danielklann.com/excel/sen...s_the_body.htm which can be used for sending a range in the body of an e-mail. The only constraint is that you must have Outlook in order to use it.

Regards,
Daniel

[Zack Barresse]

Hi Dan,

That's some beautiful code.

A question, does your code automatically email the message out, or does it stop at the message and wait for the user to send the mail?

[XL-Dennis]

Hi dk,

Some people have reported to me problems with Your solution in Outlook 2003.

Have You experienced any problem with this version?

Take care and it's good to see You here too,
Dennis

[Daniel Klann]

Hi Dan,

That's some beautiful code.

A question, does your code automatically email the message out, or does it stop at the message and wait for the user to send the mail?

The code on the site will display the message although it can be easily changed:

Change oOutlookMessage.Display to oOutlookMessage.Send

Hi dk,

Some people have reported to me problems with Your solution in Outlook 2003.

Have You experienced any problem with this version?

Take care and it's good to see You here too,
Dennis

Hi Dennis,

Not had anyone coming to me with problems. What are the particular problems that you've heard of?

Dan

[XL-Dennis]

Dan,

I will let You know asap as I can't recall it correct now.

Like Excel I have some memory-leaks so nowdays I know how Excel feel...

Kind regards,
Dennis

[Zack Barresse]

Hi,

EDITED : Zack, I think the code you referred to was for saving a range as a GIF, not for e-mailing.....

That might be. It's at least set up that way on my pc at work. I might have coupled the two macros. It copies my chart as a gif then sends it out with outlook. I'm not there, but I can check in the morning. Sorry for the confusion.

[bturner2]

Thanks for the all the answers. Yes firefyter it is possable that there could be up to 200 sheets with 50 in the named cell. I will see what I cant figure out about adding a loop in there to get it to work. I will let you know in the next day how the e-mail thing and the sheet checking went.

THanks again for all the help.

[Krishna Kumar]

I think Daniel's Code is for Microsoft Outlook. Is there any Code for Outlook Express?

Kris

[Daniel Klann]

I think Daniel's Code is for Microsoft Outlook. Is there any Code for Outlook Express?

Kris

Hi Kris,

Unfortunately Outlook Express does not support automation in the way Outlook does i.e. it cannot be controlled so easily and with precision. However, there is some code on Ron De Bruin's site which shows how to send values (no fancy formatting or anything) in the body of an OE message : http://www.rondebruin.nl/mail/oebody.htm.

HTH
Dan

[Ivan F Moala]

That might be. It's at least set up that way on my pc at work. I might have coupled the two macros. It copies my chart as a gif then sends it out with outlook. I'm not there, but I can check in the morning. Sorry for the confusion.

Hi firefytr

I think the code you refer to is this one

http://www.xcelfiles.com/EmailImg.html

Which emails a selection as an Image....advantages being you can include
shapes, objects etc and Ranges that are not hidden eg Autofilters.