need the VBA equivalent of........... (SOLVED)

[kskinne]

I need the VBA code equivalent of pressing the 'email' button on the excel toolbar, and emailing the contents of a sheet to someone, not as an attachment, but rather having the sheet appear in the message portion of the email. I tried doing this while recording a macro, but it doesn't record any code at all. Any help would be appreciated, thanks

kevin
This message was edited by kskinne on 2002-10-29 10:01

 

[Damon Ostrander]

Hi Kevin,

I don't believe that what the email button does can be accessed through the Excel object model. When I needed to access this from VBA in the past I had to use SendKeys to send keystrokes to the Excel interface. Here is the code I used:

Sub SendWorksheet()

Dim i As Integer

'activate the desired worksheet here
'Worksheets("By Row").Activate

SendKeys "%(FdM)", Wait

[a1].Select
SendKeys "+{TAB}" 'tab to Subject field

'Select previous subject text
SendKeys "^a"

SendKeys "Worksheet " & ActiveSheet.Name

SendKeys "+{TAB}" 'tab to CC field
SendKeys "+{TAB}" 'tab to To field

'Delete recipients
SendKeys "^a"

'Put email address in To field
SendKeys "Kevin@xyz.com"

Application.Wait Now + 1 / 86400

'Send email
SendKeys "%s", Wait

End Sub

 

[kskinne]

wow, that works great, thank you! I have one other question though - would it be possible to have an inputbox pop up during the process to ask for a send to address that could then be sent as keystrokes to the excel interface, or is this not possible in this case? otherwise, my send to address will always be the same unless I alter the code every time i want to send to a new address.

thanks again for the help,
kevin

 

[kskinne]

wow, that works great, thank you! I have one other question though - would it be possible to have an inputbox pop up during the process to ask for a send to address that could then be sent as keystrokes to the excel interface, or is this not possible in this case? otherwise, my send to address will always be the same unless I alter the code every time i want to send to a new address.

thanks again for the help,
kevin

 

[kskinne]

I guess I answered my own question, just wanted to follow up with this - I see now that all I have to do is have the code prompt me for an email address through an inputbox, and then give the email address a name in the code by insert a line like this:

SendToName = InputBox("Please enter send-to email address")

Then in the code, instead of using:

SendKeys "Kevin@xyz.com"

I can just use:

SendKeys SendToName

This way I can be prompted to enter a new send to address each time I run the code.

Thanks for the help to get me started on this - no way I could have done this without some assistance!

kevin

 

[Damon Ostrander]

Hi Kevin,

Yes, you can enter the email address using an InputBox. Just replace the code

SendKeys "Kevin@xyz.com"

with something like this:

Dim eAddress As String
eAddress = InputBox("Enter email address", _
"Recipients", "kevin@xyz.com")
SendKeys eAddress


Damon