Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EMail Messages (2000)

    <P ID="edit" class=small>(Edited by charlotte on 20-Sep-05 05:48. to activate link)</P>I am trying to get Access to send e-mails and am using the code off the Microsoft site at http://support.microsoft.com/default.aspx?...kb;en-us;318881. All I've done is replace the references to tables and fields. I get a "Compile Error: User-defined type not defined." error with the line "Sub SendMessages(Optional AttachmentPath)" highlighted.

    Any help?

    Thanks,
    Eric

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

    Re: EMail Messages (2000)

    Have you set a reference to the Microsoft Outlook 9.0 Object Library in Tools | References...?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: EMail Messages (2000)

    There's something in the routine that isn't compiling on your machine. The code contains references to database and recordset objects but doesn't specify DAO.Database and DAO.Recordset, so that may be where the syntax checker is choking. If you have the ADO reference set as well as the DAO reference, you must specifically declare objects to indicate the object model they belong to.. Since the code is DAO and the default reference in 2000 is ADO, that may also be the problem.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Yes, I have.

    <P ID="nt"><font size=-1>(No Text)</font>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    Thanks, but I'm not that advanced. Looked easy enough to copy the code.

    How would I fix that?

    Thanks,
    Eric

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

    Re: EMail Messages (2000)

    1. Set a reference in Tools | References... to the Microsoft DAO 3.6 Object Library.
    2. Change the lines

    Dim MyDB As Database
    Dim MyRS As Recordset

    to

    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    Thanks, that seems to help. Now it's giving me the same message for my reference to a query in the CC section. I put a --> where it's stopping. Here's what I have:

    Option Compare Database
    Option Explicit

    Sub SendMessages(Optional AttachmentPath)

    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim TheAddress As String

    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset("qryNotification")
    MyRS.MoveFirst

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    Do Until MyRS.EOF
    ' Create the e-mail message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = MyRS![EmailAddress]

    With objOutlookMsg
    ' Add the To recipients to the e-mail message.
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo

    ' Add the Cc recipients to the e-mail message.
    --> If (IsNull(Queries!qryNotificationBackorder!CCAddress )) Then
    Else
    Set objOutlookRecip = .Recipients.Add(Queries!qryNotificationBackorder!C CAddress)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, the Body, and the Importance of the e-mail message.
    .Subject = Queries!qryNotificationBackorder!Subject
    .Body = Queries!qryNotificationBackorder!Released
    .Importance = olImportanceHigh 'High importance

    'Add the attachment to the e-mail message.
    If Not IsMissing(AttachmentPath) Then
    Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

    ' Resolve the name of each Recipient.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub


    Thanks for your help.
    Eric

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

    Re: EMail Messages (2000)

    You can't refer to queries that way. You'll have to open a recordset on qryNotificationBackorder and retrieve the CCAddress from it the same way the EMailAddress is retrieved from qryNotification. You must do this after the loop that cycles through the records of MyRS.

  9. #9
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    Thanks, I will try that.

    Eric

  10. #10
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    Here's what I have now. It doesn't seem to like my query though. I am guessing this won't work with parameter queries? I'll work around that.

    Thanks for all your help.
    Eric

    Option Compare Database
    Option Explicit

    Sub SendMessages(Optional AttachmentPath)

    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim TheAddress As String
    Dim TheCCAddress As String
    Dim TheSubject As String
    Dim TheBody As String


    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset("qryNotificationBackorder")
    MyRS.MoveFirst

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    Do Until MyRS.EOF
    ' Create the e-mail message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = MyRS![EmailAddress]
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheCCAddress = MyRS![CCAddress]
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheSubject = MyRS![Subject]
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheBody = MyRS![Released]

    With objOutlookMsg
    ' Add the To recipients to the e-mail message.
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo

    ' Add the Cc recipients to the e-mail message.
    If (IsNull(TheCCAddress)) Then
    Else
    Set objOutlookRecip = .Recipients.Add(TheCCAddress)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, the Body, and the Importance of the e-mail message.
    .Subject = TheSubject
    .Body = TheBody
    .Importance = olImportanceHigh 'High importance

    'Add the attachment to the e-mail message.
    If Not IsMissing(AttachmentPath) Then
    Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

    ' Resolve the name of each Recipient.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: EMail Messages (2000)

    That syntax won't work with parameter queries. If you open a parameter query in code, you have to pass the parameters to it in code, something like this:

    <code>Dim qry As DAO.QueryDef

    Set qry = MyDb.QueryDefs("qryNotificationBackorder")
    qry.Parameters(0) = <somevalue>
    set MyRs = qry.OpenRecordset(dbOpenSnapshot)</code>
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    Thanks for all the help. I've got this set up the way I want and it works when I hit "run" in the VB window. However, I'm having a terrible time trying to figure out how I can get this to run as part of a macro.

    Eric

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

    Re: EMail Messages (2000)

    If you change your procedure (sub) to a function, you can call it in a macro, using the RunCode action.

    BTW I (almost) never use macros, I find VBA code much easier to debug and to maintain.

  14. #14
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EMail Messages (2000)

    I thought about that but am not sure how. Can I just change "sub" to "function"? Just tried and it doesn't want to work.

    My problem is I am not proficient in VBA. I'd like to learn, but haven't had the opportunity yet.

    Thanks,
    Eric

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

    Re: EMail Messages (2000)

    Just change Sub to Function both in the first and in the last line (if you had had Exit Sub instructions, you'd have had to change those to Exit Function).

Page 1 of 2 12 LastLast

Posting Permissions

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