Is this even possible???

[Legacy 237453]

Hi chaps.

I just wanted to know if this is even possible before i begin attempting to write the VBA for it...

I have a excel document that is used by around 16 people. It consists of a table that is filled out during the night, a email distribution list and a email subject with a date that automatically changes to suit the previous nights date.

Every mornnig the operator working has to manually copy and paste over the details into a outlook email ready to send.

Its there a way i can create a button to simply click and a outlook session will automatically open with all the details copyed over? I guess i mean can i creat a macro that works beetween excel and outlook with a click of a button?

Please let me know if i have not explained clearly

Thanks for reading!
Craig

 

[VoG]

Take a look here Mail from Excel and make/mail PDF files (Windows)

 

[Legacy 237453]

Thanks for the reply!.... This is absolutly perfect. Im new to VBA and i get so excited when i see its uses.
Ive used the Mail Range or Selection code and this does 80% of what i want it to do.
I dont suppose there is a way to have the body of the email display the table instead of having the worksheet put into a attachment?

Thanks a lot
Craig

 

[VoG]

It is if you are using Outlook. See the sectionOutlook object model (body)

 

[Legacy 237453]

ADVERTISEMENT

thank lol i should have read the whole lot before steaming on ahead and using the wrong one

i need the email to send from a differant account other than the defult one? is this possible?
Ive experimented with adding .from = "systemsoperations@kingfisher.co.uk"

Thsi didnt work... any ideas?

    With OutMail
        .From = "[EMAIL="systemsoperations@kingfisher.co.uk"]systemsoperations@kingfisher.co.uk[/EMAIL]"
        .To = "[EMAIL="craig.harbut@kingfisher.com"]craig.harbut@kingfisher.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = RangetoHTML(rng)
        .Display
    End With

 

[VoG]

I think that you can use .Sender (Outlook 2007 and later) but I'm not sure. I think this is covered somewhere on Ron's site.

 

[Legacy 237453]

ADVERTISEMENT

I think that you can use .Sender (Outlook 2007 and later) but I'm not sure. I think this is covered somewhere on Ron's site.

Found it... Thanks

.SentOnBehalfOfName = """SenderName"" <Reply@Address.com>"

 

[VoG]

That's what I was looking for. I'll try to memorise it

 

[Legacy 237453]

VoG i have one more question if you dont mind

The subject is currently whatever i type into the VBA

.Subject = "This is the Subject line"

this is not ideal as the subject should display the previos days date.
In the work sheet i have cell B7 with the following formula -

="SAP F&R Poland Milestones for "&SUBSTITUTE(TEXT(TODAY()-1,"dddd ""the"" dzz mmmm yyyy"),"zz",MID("thstndrdth",MIN(9,2*RIGHT(DAY(TODAY()-1))*(MOD(DAY(TODAY()-1)-11,100)>2)+1),2))

This displays -

SAP F&R Poland Milestones for Sunday the 14th April 2013 Is there any way i can get the subject to autofill with this information aswell? In other words can i have the subject copy from Cell B7 ? Thanks Craig! </SPAN>

<TBODY>
</TBODY>

 

[Legacy 237453]

Can anybody shed some light on the above please?