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

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,147,508
Messages
5,741,571
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top