Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Mail when spreadsheet opened (MS Office 2003)

    I have a spreadsheet that uses an MS Access query to retrieve five items of data. When I use this spreadsheet (Temination) it Auto_Opens with the query, I then want it to do a Sent To, with an attachement, the Address is filled, and with about two lines of information in the body.

    How can I do this with a Macro? I tried doing record but it doesn't work right.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Mail when spreadsheet opened (MS Office 2003)


  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Mail when spreadsheet opened (MS Office 2003)

    I copied one of the examaples and I get - "User-defined type not defined" and it points to " Dim OutApp As Outlook.Application". I am not a program so not sure what to do about this.

    Sub Mail_ActiveSheet_Outlook()
    'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail when spreadsheet opened (MS Office 2003)

    I'm sure you're not a program <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Did you read the instruction in the comment? You're supposed to set a reference (in Tools | References in the Visual Basic Editor) to the Microsoft Outlook 11.0 Object Library.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Mail when spreadsheet opened (MS Office 2003)

    Ouch!

    yes, got that part working. I think I have only one last problem I need help with and reset should be easy. I need to see if I can save it under a different name (line 9) . Evey time I run it, it comes back and say's "Can't fine the file". Do I need to add a path name or something?


    Sub Mail_ActiveSheet_Outlook()
    'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wb As Workbook
    'Dim strdate As String
    'strdate = Format(Now, "dd-mm-yy h-mm-ss")
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    With wb
    .SaveAs "Termination2.xls"
    MsgBox "File" & SaveAs

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = "Daniel Reyes"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Hi 1"
    .Attachments.Add ("tabsnasdatabase$edgarnewcigarsTermination2.xl s")
    'You can add other files also like this
    '.Attachments.Add ("C:test.txt")
    .Send 'or use .Display
    End With
    '.ChangeFileAccess xlReadOnly
    'Kill .FullName
    .Close False
    End With
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail when spreadsheet opened (MS Office 2003)

    > Do I need to add a path name or something?

    Why don't you try?

Posting Permissions

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