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

    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

  2. #2
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    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

  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
  •