PDA

View Full Version : Advice: Email Using Composition Header and Envelope Toolbar Button



Len Piwowar
06-09-2004, 08:59 AM
Excel 2000 VBA
I wrote some code to check the current excel sheet for my name if found a msgbox is shown asking for a response, ex; Yes, No, Vac after a response is made: (Excel > File > Send To > Mail Recipient ) DISPLAYS
This makes visable the e-mail composition header and the envelope toolbar
I use the following code:
ActiveWorkbook.EnvelopeVisible = True
I can't figure out how to write a variable to the TO: and Subject: objects so when the commposition header is true the To: E-mail address will allready have a mail address in it.
As seen below I use SendKeys since I could not find the properties for the e-mail composition header and the envelope toolbar. SendKeys does work fine except when BCC; is opened the To: and CC: mis-places. I would rather be able to write the varible to the To: & Subject: prompts. Thanks in advance :help


SendEmail:
'Open &Mail Recipient CommandBar
If Range("A5").Value = "Y/N" Then
'ActiveWorkbook.EnvelopeVisible = True
Dim sTo As String
Dim sCC As String
Dim sSubject As String
Dim CurrActiveSheet As String

CurrActiveSheet = ActiveWorkbook.Name
Application.Goto Range("a1")
ActiveWorkbook.EnvelopeVisible = True
SendKeys "~"
AppActivate "Microsoft Excel" 'Give control back to excel
Worksheets(CurActiveSht).Activate

Range("A1").Select

Application.SendKeys "+{TAB}+{TAB}+{TAB}", True
sTo = "RecipientName"
SendKeys "{HOME}+{END}{DEL}" & sTo & "{TAB}", True
sCC = ""
SendKeys "{HOME}+{END}{DEL}" & sCC & "{TAB}", True
'sSubject = "Response = " & AskDayFound(1) & AnsIs(1) & AskDayFound(2) & AnsIs(2) & AskDayFound(3) & AnsIs(3) & AskDayFound(4) & AnsIs(4) & AskDayFound(5) & AnsIs(5) & AskDayFound(6) & AnsIs(6) & AskDayFound(7) & AnsIs(7)
sSubject = ListChoice & " " & AskDayFound(1) & AnsIs(1) & AskDayFound(2) & AnsIs(2) & AskDayFound(3) & AnsIs(3) & AskDayFound(4) & AnsIs(4) & AskDayFound(5) & AnsIs(5) & AskDayFound(6) & AnsIs(6) & AskDayFound(7) & AnsIs(7)
SendKeys "{HOME}+{END}{DEL}" & sSubject & " For " & CurrActiveSheet & "~", True
End If
TheEnd:
End Sub

mark007
06-09-2004, 09:20 AM
It appears that from Excel 2002 and above you can use VBA to do it but before that it's not available:

http://support.microsoft.com/default.aspx?scid=kb;en-us;816644&Product=ol2002

Anne Troy
06-11-2004, 01:46 PM
Len...did we get anywhere with this?

Len Piwowar
06-11-2004, 06:16 PM
It appears I'll have to continue to use SendKeys untill I upgrade to Excel 2002.
Thanks for the Help
Len