sending an email from excel

[plunky]

Howdy all-

I found the magic macro i needed to be able to send an email from excel by clicking a button, and to populate the subject and body of the email with certain cells. The purpose of my file will be to input the time i'm leaving work for the day, click the email button, and have the email pop up with the time in the body. The problem i'm getting is the time shows up in the body of the email as a number...the hh:mm format is gone. Is there a way to keep the formatting of the cell you want to send in the email?

My second question is, i want to do this every day, so i'll have different cells in the excel file that i want to send in the email every day. Can i modify the macro to send the information from different cells, or am i restricted to one cell?

 

[BrianExcel]

1. What is the number showing up? Is it the same time but in a diff. format or a completely different number?

2. You can code to look for different cells if you need to. It's just a matter of setting the worksheet up correctly and coding based on that correct format.

BUT, rather than entering the time in a worksheet, why not just program the email body with a time/date stamp so it (1) enters the time/date stamp in the email, and (2) also enters it in the worksheet, if you even have to have it in the worksheet?

 

[plunky]

The number in the cell is a time, formatted to say, for example, 4:50 PM, but it's showing up in the email as 0.7188888 or something. I'm sure there's a reason it's showing up like that, I just haven't tried to figure out the formula lol...my excel file acts kind of like a timecard, where i put in a start time and end time myself for a certain project. If i know i'm gonna leave at 4:50, i'll put that in at any time during the day, it could be in the morning or afternoon.

Hope that answered your question about my question! If need be, i can upload or email part of my file to show you exactly how it's set up. I love working with VBE, but man it's touchy!

 

[BrianExcel]

Feel free to email it. You can either message me for my email or upload to a public site like box.net. I'll take a look.

 

[plunky]

did you get my message? not sure if it went thru lol

 

[BrianExcel]

Sorry. Didn't get anything from you.

 

[shippey121]

rather than using a cell to find the time cant you use

.Body = Now()

which will insert the time and date ?

 

[plunky]

that'll just insert whatever time it is at the moment, won't it? i usually have my timecard filled out by noon, and don't leave until 4 or 5, so i would just want it to send the time that i have in a certain cell.

 

[daverunt]

Doesn't using text work?

.body = Sheets("Sheet1").Range("A1").Text