Advice: Email Using Composition Header and Envelope Toolbar Button
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
Code:
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
