Results 1 to 3 of 3
  1. #1
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29

    Running a module

    I need some help. I am trying to run a module in my database in a macro I have that runs with the onclick function on the button. I need the module to run with this macro when that button is clicked. So I am trying to have the macro run the module.

    What I am doing right now is using the Runcode action in the macro, then entering the name of the module for the Function Name. However I get an error when I run the macro. It is:

    The object doesn't contain the Automation object 'Email'.

    You tried to run a VB procedure to set a property of method for an object. However, the component doesn't make the the property or method available for Automation operations.

    I am wondering, should I be using a different action to get the module to run? Below is my code:

    -------------------------------------------------
    Option Compare Database
    Option Explicit
    Sub SendMessages(Optional AttachmentPath)
    Dim MyDB As Database
    Dim MyRS As 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("tblMailingList")
    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(Forms!frmMail!CCAddress)) Then
    Else
    Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, the Body, and the Importance of the e-mail message.
    .Subject = Forms!frmMail!Subject


    .Body = Forms!frmMail!MainText
    .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

    In the debug window I then have:

    SendMessages

    -------------------------------------------

    I saved this code as a module. It will send an email through outlook after a form opens(which I include in the macro).

    So any ideas how to make this module run within the macro?? Thanks!

  2. #2
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    There's more than one way to send an email message via Outlook. The following works perfectly for me (the email is actually generated in the last line of the code segment):


    'build address list for statistics email
    EmailTo = ""
    SQLstr = "SELECT * FROM LookupTable WHERE (([Category] = 'Email Address') AND ([SubGroup] = 'To'));"
    Set rst = CurrentDb.OpenRecordset(SQLstr, dbOpenDynaset)
    With rst
    If Not (.BOF = True And .EOF = True) Then
    .MoveFirst
    Do While Not .EOF
    EmailTo = EmailTo & !textvalue & ";"
    .MoveNext
    Loop
    End If
    End With

    EmailCc = ""
    SQLstr = "SELECT * FROM LookupTable WHERE (([Category] = 'Email Address') AND ([SubGroup] = 'Cc'));"
    Set rst = CurrentDb.OpenRecordset(SQLstr, dbOpenDynaset)
    With rst
    If Not (.BOF = True And .EOF = True) Then
    .MoveFirst
    Do While Not .EOF
    EmailCc = EmailCc & !textvalue & ";"
    .MoveNext
    Loop
    End If
    End With

    SubjectStr = "Data File Processed: " & Get_County & " " & Get_Mailing

    DoCmd.SendObject acSendReport, "Data File Statistics", acFormatRTF, EmailTo, EmailCc, , SubjectStr, "See Attached for Details"

  3. #3
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    Alright, I found some help over at utteraccess.com on this.

    I changed two code lines. The first line to:

    Public Function SendMessages ()

    and the last line from a Sub line to a Function Line.

    Then I was able to use it in a macro with the Runcode action. The Function name was then SendMessages ().

    Works great now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I use a MODULE in a Cross-Tab Query?
    By timo1999 in forum Modules
    Replies: 2
    Last Post: 12-13-2014, 04:51 PM
  2. Error in Module
    By Alex Motilal in forum Modules
    Replies: 9
    Last Post: 01-21-2011, 09:21 AM
  3. Running module from Macro
    By Harley Guy in forum Modules
    Replies: 6
    Last Post: 10-27-2010, 11:05 AM
  4. need help with import Module
    By marubal21 in forum Modules
    Replies: 1
    Last Post: 07-22-2010, 12:23 AM
  5. Module not found
    By nooby in forum Modules
    Replies: 1
    Last Post: 12-02-2009, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums