Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ms Access + Outlook (2007)

    1) Is het mogelijk om een outlook macro te activeren vanuit MS Access
    2) Uit het boek "MS ACCESS Hacks ", heb ik gebruik gemaakt van de procedure beschreven onder "Hacks 69", met betrekking tot "Send Data Through Outlook Automatically.
    Hierbij wordt uitgegaan van een Access datatabel met twee textvelden:
    In het eerste veld komt het emailadres te staan aan wie de message dient gestuurd
    in het tweede veld komt de naam van de Access datatabel te staan die als Attachement dient meegestuurd.
    Ik volg precies wat in het voorbeeld staat, met dit verschil dat ik hier een tabel als bijlage wenst te versturen i.p.v. een rapport zoals in het voorbeeld het geval is.
    Om die reden werd in het statement DoCmd.OutputTo acReport, acReport vervangen door acOutputTable
    Telkens ik het programma doet lopen, krijg ik een fout nr 278 betrekking hebbende op de statement: olMail.Recipients.Add varmail.
    Hierin bevat varmail mijn emailadres : jacques.pardo@pandora.be
    Wat is hieraan fout ?

    Ziehier de source:

    Public Sub SendOutlookEmail()
    Dim db As DAO.Database
    Dim ReportRs As DAO.Recordset
    Dim EmailRS As DAO.Recordset

    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem

    Dim EmailColl As Collection
    Dim varEmail As Variant
    Dim FileName As String

    Set olApp = New Outlook.Application
    Set db = CurrentDb
    Set ReportRs = db.OpenRecordset( _
    "Select Report_Name from tbl_Email Group by Report_Name")

    ReportRs.MoveFirst

    While Not ReportRs.EOF
    Set EmailColl = New Collection
    Set EmailRS = db.OpenRecordset( _
    "Select Email_Address from tbl_Email Where Report_Name = " & """" & _
    ReportRs.Fields(0).Value & """" & ";")
    EmailRS.MoveFirst
    While Not EmailRS.EOF
    EmailColl.Add EmailRS.Fields(0).Value
    EmailRS.MoveNext
    Wend

    EmailRS.Close
    Set EmailRS = Nothing

    Set olMail = olApp.CreateItem(olMailItem)
    olMail.subject = "Monthly Report"
    For Each varEmail In EmailColl
    olMail.Recipients.Add varEmail
    Next
    olMail.Body = "Your Monthly Report is attached"
    FileName = "C:Reports" & ReportRs.Fields(0).Value & ".rtf"
    DoCmd.OutputTo acReport, ReportRs.Fields(0).Value, _
    acFormatRTF, FileName
    olMail.Attachments.Add FileName, olByValue, 1, "Monthly Report"
    olMail.Send

    Set olMail = Nothing
    Set EmailColl = Nothing

    ReportRs.MoveNext
    Wend

    ReportRs.Close
    Set ReportRs = Nothing

    Set olApp = Nothing


    Set db = Nothing
    End Sub

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

    Re: Ms Access + Outlook (2007)

    For those Loungers who don't know Dutch, here is a translation of the above post:

    1) Is it possible to activate an Outlook macro from MS Access
    2) I've used a procedure from the book "MS ACCESS Hacks" described under "Hacks 69", concerning "Send Data Through Outlook Automatically".
    It uses an Access data table with two text fields:
    The first field contains the e-mail address of the recipient of the message
    The second field contains the name of the Access data table that is to be sent as an attachment.
    I'm following the example exactly, except that I want to attach a table instead of a report as in the example.
    For that reason, I replaced acReport with acOutputTable in DoCmd.OutputTo acReport
    Each time I run the program, I get error number 278 at the statement olMail.Recipients.Add varmail.
    varmail contains my e-mail address ...@...

    Here is the source:
    ...

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

    Re: Ms Access + Outlook (2007)

    Please ask your questions in English - this is an international forum in which only a small number of participants know Dutch.

    Click in the line

    olMail.Recipients.Add varEmail

    and press F9 to set a breakpoint. When you run the code, execution will pause at this line. You can inspect the value of variables such as varEmail by hovering the mouse pointer over them. Does varEmail contain a valid e-mail address at that point?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ms Access + Outlook (2007)

    1) Can i activate a outlook macro from inside ms Access
    2) The varmail contains my emailaddress = jacques.pardo@pandora.be

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

    Re: Ms Access + Outlook (2007)

    1) I'd copy the Outlook code into your database and adapt it for use with Automation.
    2) The code looks OK, so we'd have to see the database to know what goes wrong.

Posting Permissions

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