Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    42

    Unanswered: Email to Specific Address based on form field

    Hi all, this one is a two parter that I need some help on. I am trying to email to a specific person based on a no. in a field which references a query that houses the email address. I can do the basic email in a macro, but because of the specific identifier and the need to have it pull the Worksheet No. into the email...I have no clue how to do this in plain access, which means coding it. Unfortunately Im not very good with vb although I am starting to catch on thanks to many people's help in here.

    Part 1: Sending the email..
    I have a worksheet no: [CRID]
    I have a query with the email list: [14_03-SQEMGboth]
    I have a field that touches both: [MtrlGrp]
    The email address field is: [14_03-SQEMGboth]![Email]
    The notify button [Notify] would be on the main form: [00_02_03-PRall]
    The box that would tell it where to look is [MtrlGrp], which links to the query that contains the email address to pull.

    Part 2: Putting the worksheet no. in the email.
    Is there anyway to tell it to put the [CRID] number in the email.

    "New worksheet [CRID] has been entered. Please review in the database." or something like that.

    Its a bit tricky and Im not sure it can be done.

    Any help would be appreciated.
    Thanks-




    Example:

  2. #2
    Join Date
    May 2003
    Posts
    144
    Tina,

    Are you thinking about having the message as part of the content of your email or can you have it as the subject title of the email.

    SHK

  3. #3
    Join Date
    Jul 2003
    Posts
    42

    Subject Line is fine

    If I can get the email address into the subject line that would be great. If not..then message will be ok too. I just need to try to get it in there.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    below is a generalised outlook mailer that i usually put in a global module so any form can call it. you just need to call it with your data. e.g.

    isDraft = true or false to suit what you want to do
    isTo = [14_03-SQEMGboth]![Email]
    isSubj = "New Worksheet " & [CRID]
    isBody = New worksheet " & [CRID] & " has been entered. Please review in the database."

    ignore the optional isFile unless you want to attach a copy of [CRID]

    the call is
    if izyMailer(isDraft, isTo, isSubj, isBody) then
    msgbox "Mailing OK"
    else
    msgbox "Mailing error"
    end if

    izy

    Code:
    Public Function izyMailer(isDraft As Boolean, isTo As String, isSubj As String, isBody As String, Optional isFile As String = "NONE") As Boolean
        'function requires:
        '   isDraft TRUE saves the mail in drafts, FALSE sends immediate
        '   isTo    the recipient's mail address (addrFirst; addrMiddle; addrLast   if a distribution list)
        '   isSubj  the mail subject
        '   isBody  the mail body text
        'and optionally accepts
        '   isFile  the drive:/path/name.extension of a file to attach
        'caller is responsible for validating isFile
        'function returns TRUE on success, else FALSE
        
        
        On Error GoTo err_izyMailer
        
        Dim objOutlook As Outlook.Application
        Dim objOutMail As Outlook.MailItem
        Dim objOutDist As Outlook.Recipient
        Dim objOutFile As Outlook.Attachment
        
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutMail = objOutlook.CreateItem(olMailItem)
        
        Set objOutDist = objOutMail.Recipients.Add(isTo)        'make distribution list 
        objOutDist.Type = olTo
    
        objOutMail.Subject = isSubj                        'make subject
    
        objOutMail.Body = isBody                            'make message body
        
        If Not isFile = "NONE" Then Set objOutFile = objOutMail.Attachments.Add(isFile)   'attachment
        
        objOutMail.Save                             'save as draft
        If Not isDraft Then objOutMail.Send         '...and possibly send
        
        izyMailer = True                            'success return
        
    exit_izyMailer:
        Set objOutDist = Nothing                    'tidy up
        Set objOutFile = Nothing
        Set objOutMail = Nothing
        Set objOutlook = Nothing
        
        Exit Function                               'and exit
     
    err_izyMailer:
        izyMailer = False                           'failure return
        MsgBox Err.Description, vbCritical, "izyMailer Error"
        Resume exit_izyMailer
     
    End Function

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    duplicate post deleted. izy

  6. #6
    Join Date
    Jul 2003
    Posts
    42

    Almost there...=)

    Is there a way to tell it to pull the email address in Query [14_03-SQEMGboth] based on the [SQEID] number it finds in subform [SFSQE] on the worksheet form [00-PRall]

  7. #7
    Join Date
    Jul 2003
    Posts
    42

    Resolved.

    Got this resolved I actually ended up getting it to work in a reg. Im sharing it in case anyone ever needs it. Here's the Access macro.

    Action: Sendobject

    Arguments:

    Object Type:Blank 'not sending a document with the email
    Object Name: Blank 'same
    Output Format: Blank 'same
    Output To: =[SFSQE]![Email] 'chose the email address in the subform
    CC: name@company.com 'cc this persons email addy
    Subject: ="New Worksheet " & [CRID]
    Message: ="New worksheet " & [CRID] & " has been entered as of" & Now() & ". Please review in the database."
    EditMessage: NO 'change if you want email editable
    Template File: Blank 'not needed for this

    I had to change it so my subform had a hidden email field so its there, but not showing, thus the email can pull it to send to. =)

Posting Permissions

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