Consulting

Results 1 to 4 of 4

Thread: Advice: Email Using Composition Header and Envelope Toolbar Button

  1. #1

    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

    [vba]
    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
    [/vba]

  2. #2
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    619
    Location
    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...Product=ol2002
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net | Professional Office Developers Association

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Len...did we get anywhere with this?
    ~Anne Troy

  4. #4
    It appears I'll have to continue to use SendKeys untill I upgrade to Excel 2002.
    Thanks for the Help
    Len

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •