Results 1 to 12 of 12
  1. #1
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10

    Question Sending E-Mail via G-Mail with attachments or from a query.


    Hello, I am looking for some direction with sending an email from Access. I have successfully been able to send a message without attachments and have to manually set the recipient.

    I have two things I would like to be able to accomplish.
    1- Send an email with an attachment (a report in pdf format or excel) from within Access using G-Mail.
    2- 2- Send emails from a query via G-Mail. Each record returned would have the recipients email address, and a number of items outstanding. (The subject would contain preformatted text with the result inserted) so that each email is different based on its unique results.


    Any assistance is appreciated.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    From access I don't think you can do anything but mask the sender's email address as a g-mail address (and I'm not sure you can do that I've never tried). There may be some controls I haven't used that will allow it and if you can figure out how to do that part (if your emails have to look like they came from gmail to the uninitiated) I can help you with cycling through records in a query to populate the email

  3. #3
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    I have used an example from another post that will allow me to send emails direction from a G-Mail account using the Microsoft CDO. The issues is getting the attachtment to work and the other is cycling through the records. Your help is appreciated.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    I'm assuming you want to use the NOTIFY query to use as your email base. Are you physically typing in an attachment in the form you have created? or is there a default set of text you can send depending on how many errors and the state specified?

    The simplest way to cycle through records is this (using your notify query as a base)

    Code:
    Dim db As database
    Dim rst As Recordset
    Dim sEmployeeName As String
    Dim sEmailAddress As String
    Dim iErrorCount As Integer
    Dim sState As String
    
    Set db = CurrentDb
    Set rst = db.openrecordset("Notify")
    
    rst.MoveFirst
    Do While rst.EOF <> True
        sEmployeeName = rst.Fields("EmployeeName")
        sEmailAddress = rst.Fields("E-Mail Address")
        iErrorCount = rst.Fields("number of outstanding errors")
        sState = rst.Fields("state")
    
        [INSERT YOUR EMAIL ROUTINE HERE WITH THE VARIBLES FROM YOUR NOTIFY QUERY]
        rst.MoveNext
    Loop
    You may have to add some references to get this working but it should cycle through your records and if you have a pre determined item you want to send as an attachment you'd just have to have the path of the item in your query and read that as well, then in your email (I don't know the method you're using very well) you should be able to substitute in your variable for the attachment.

  5. #5
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Thanks for you quick reply i will have to play around with that this week. I will follow up by friday.

    B

  6. #6
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Hello, I have attempted to use the above to work from however when i try to compile the vba i received the following error. "Compile Error: User-defined type not defined?"

    Code:
    Private Sub EMailButton_Click()
    Dim db As database
    Dim rst As Recordset
    Dim sEmployeeName As String
    Dim sEmailAddress As String
    Dim iErrorCount As Integer
    Dim sState As String
    Set db = CurrentDb
    Set rst = db.openrecordset("Notify")
    rst.MoveFirst
    Do While rst.EOF <> True
        sEmployeeName = rst.Fields("EmployeeName")
        sEmailAddress = rst.Fields("E-Mail Address")
        iErrorCount = rst.Fields("number of outstanding errors")
        sState = rst.Fields("state")
    'start email
        Dim cdomsg
      Set cdomsg = CreateObject("CDO.message")
      With cdomsg.Configuration.Fields
        .Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2 'NTLM method
        .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/con...smptserverport") = 587
        .Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "emailaddy@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "Password"
        .Update
      End With
      ' build email parts
    
      With cdomsg
        .To = Me!EmailAddr
        .From = "emailaddy@gmail.com"
        .Subject = Me!Subject
        .TextBody = Date & " ""You have" & " " & [Number of ourstanding errors] & " ""errors unresolved."
        .Send
      End With
      Set cdomsg = Nothing
    'end email
        rst.MoveNext
    Loop
    End Sub
    Any assistance would be appreciated.
    B


    Quote Originally Posted by rpeare View Post
    I'm assuming you want to use the NOTIFY query to use as your email base. Are you physically typing in an attachment in the form you have created? or is there a default set of text you can send depending on how many errors and the state specified?

    The simplest way to cycle through records is this (using your notify query as a base)

    Code:
    Dim db As database
    Dim rst As Recordset
    Dim sEmployeeName As String
    Dim sEmailAddress As String
    Dim iErrorCount As Integer
    Dim sState As String
     
    Set db = CurrentDb
    Set rst = db.openrecordset("Notify")
     
    rst.MoveFirst
    Do While rst.EOF <> True
        sEmployeeName = rst.Fields("EmployeeName")
        sEmailAddress = rst.Fields("E-Mail Address")
        iErrorCount = rst.Fields("number of outstanding errors")
        sState = rst.Fields("state")
     
        [INSERT YOUR EMAIL ROUTINE HERE WITH THE VARIBLES FROM YOUR NOTIFY QUERY]
        rst.MoveNext
    Loop
    You may have to add some references to get this working but it should cycle through your records and if you have a pre determined item you want to send as an attachment you'd just have to have the path of the item in your query and read that as well, then in your email (I don't know the method you're using very well) you should be able to substitute in your variable for the attachment.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    On which line are you getting the error message?

  8. #8
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Code:
    Dim db As database
    Quote Originally Posted by rpeare View Post
    On which line are you getting the error message?

  9. #9
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Anyone have any ideas on how to cycle through ha query's result. Pick out each record and email that records info to the email address within it.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    If you're erroring on

    Dim db as database

    you're missing a reference in your vb scripting window. Go to tools>references

    The reference you want is

    Microsoft Office 12.0 Access database engine objects

    (or the version of this reference that's appropriate for you version of access)

  11. #11
    bdenton is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    I have Microsoft Office 14.0 Access database engine objects, and it still didn't work.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    I don't know then they may have changed the reference for Office 2010 (I don't have access to any version that high so I can't test for you) Just do a web search for references for 2010 and see if you can find it.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-10-2011, 09:48 AM
  2. Sending Outlook E-mail - Run-time error '429'
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 03-01-2011, 09:30 AM
  3. Replies: 3
    Last Post: 02-21-2011, 09:57 AM
  4. Sending mail automatically
    By carstenhdk in forum Forms
    Replies: 1
    Last Post: 05-31-2010, 04:23 PM
  5. E-mail query
    By Ize in forum Queries
    Replies: 1
    Last Post: 12-09-2009, 09:47 AM

Tags for this Thread

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