Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Ask about eMailing???

    Ok, so in this db that I have, there is a field known as "Status", the selections are "Closed, Open, Returned for Correction, and a few others".

    What I'm trying to do is when the user selects "Closed", the database will ask a question to the user of "Would you like to send the contact an email to tell them that their document is ready to be picked up?"

    There is a field on the form where the person can put in the contact name which is where this would come from. The user has another form where the contact informatio would be in, ie: Name, Phone, Location, eMail address.

    I am trying so that if the user selects, "Yes", MS Outlook would open a new email from their account with that users email address pulled in from the DB.

    Any ideas on how I can get it done?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Which part are you stuck on? The simplest way to send the email would be SendObject. If the address is on the form, you can get it from there.
    Paul

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    I'm stuck on trying to get it so say "If status = closed, then ask question if they want to email the contact".

    The email address is not on that form, just the contact first and last name. It would have to pull the email address through the ContactID Number from the other form / table.

    Thanks!

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    In the on change event of Status (assuming it's a combo box) put

    if me.StatusControlName = "closed" then

    msgbox(fill in appropiate arguments)

    if msgbox = yes (look at msgbox in help for details)

    sendobject(fill in appropriate arguments)

    end if

    else

    what to do if the status is not "closed"

    end if

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Thanks Rogue, I'll try that!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, I didn't get an email notification of your first response. As rogue points out, a Yes/No message box is the way to go.
    Paul

  7. #7
    Join Date
    Aug 2006
    Posts
    559
    pbaldy,

    Yes, trying that. I'm going to work on it and repost if I have questions or get stumped.

    Thanks!

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Ok, so I've got the module I need to send the task to email, using MS Outlook on that users account. I've tested that (just running the module) and it'll work.

    The only thing I'm trying to figure out now is:

    1. How to input the data from that particular task (ie: the data that is say on Task #1, much like the "print current record") to be put in the snapshot of the report that I did.

    2. Insert the users email which is put into the database as the default "To:"

    Code follows:

    Code:
    Sub send_email()
    
    Dim doc_name As String
    Dim email_title As String
    
    doc_name = "rptTask"
    email_title = "Ready for Pick-up"
    
    DoCmd.SendObject acSendReport, doc_name, "SnapshotFormat (*.snp)", "", "", "", email_title, "", True, ""
    
    End Sub

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    1) http://www.granite.ab.ca/access/emai...recipients.htm

    2) Not sure where the email address is. If it's on the form, you can simply refer to it: Me.EmailAddress
    Paul

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pbaldy
    1) http://www.granite.ab.ca/access/emai...recipients.htm

    2) Not sure where the email address is. If it's on the form, you can simply refer to it: Me.EmailAddress
    pbaldy,

    Thanks! So I'm going to try to add "Task.ID.=??" to print the current record to a snapshop viewer and email it.

    The second question, the email address resides in a different table but the users name (First and Last) is on the main form (Task.Log). I'm going to take a look at that code which puts the user's name in that location because I know the "Contact ID" (primary key for the contact table which contains the email address) is on that just hidden from view. So I'll try to pull that into the email.

    Sound like a plan?

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry for the delay, I didn't get an email notification of your post. Yes, that does sound like a plan. If you can't get the email on the form it sounds like you could use a recordset or DLookup to get it using the user name as criteria.
    Paul

  12. #12
    Join Date
    Aug 2006
    Posts
    559
    Ok, so the code that I put in the above post works for opening an email.

    The thing that I'm stuck on is how to pass the email address from the contact on my main form into the "To" line of the email.

    There is two things that happen:

    1. The user double-clicks in the field and it'll open another form where the user can search for the contact to add into the location. After they double-click or select the one they want to add, it passes a value (ContactID) to the main form in a box. Code for that is:
    Code:
    Forms!frmTask!Contact_ID = Me!List7
    forms!frmTask!List96.Requery
    That puts the users first and last name in the field of "Contact for Pick-up"

    2. I now have to figure out how to pass the 'email' field in from the table (tblContact) into the 'To' line of the email using the code in previous post to start the eMail in MS Outlook.

    The RowSource in the listbox, which displays the contacts first and last name, is set through a query with the code:

    Code:
    SELECT tblContact.Contact_ID, LastName, FirstName, Phone AS Name FROM tblContact WHERE (((tblContact.Contact_ID)=Forms!frmTask!Contact_ID));
    Is there a way to modify that query, creating a new one of course, to put the email address into the email's "To" line?

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure; add the field to the query and adjust the properties of the listbox to account for it (column count, widths). Then you can get the email using the column property of the listbox:

    ...List7.Column(x)

    where x is the column number, starting with 0.
    Paul

Posting Permissions

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