Results 1 to 13 of 13
  1. #1
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11

    Create button to send email based on data in tbl

    I have created a coding to send emails using excel but iam unsure how to do this using access. I have a table that contains all of the info i need in the email and I want to create a button to automatically send out emails using access..below is the sample coding I currently use in excel :


    Sub SendMail()
    '
    ' SendMail Macro
    ' created 4-5-17

    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you sure you want to send Emails?", vbYesNo, "Send Emails")
    If Answer = vbYes Then

    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    For I = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    Set olApp = CreateObject("Outlook.application")
    Set olMail = olApp.CreateItem(olMailItem)


    With olMail
    .To = Cells(I, 1).Value
    .Subject = Cells(I, 2).Value
    .BodyFormat = olFormatHTML
    .HTMLBody = Cells(I, 3).Value
    .Attachments.Add (Cells(I, 4).Value)
    .SendUsingAccount = Outlook.Session.Accounts.Item(1)
    .Display
    ''.Send
    End With

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Create a recordset and get parts of email from this to feed into your code

    https://access-programmers.co.uk/for...d.php?t=236074

    Should help


    Sent from my iPhone using Tapatalk

  3. #3
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11
    Thanks Andy49. this helped alot, I am having to issues adding the attachments to the email ("The operation failed") also I have set the code to use a different account for sending the emails but it sends from my default email account no matter if I input "1" or "2". below is the code that I have so far:

    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you Sure you want to Send Emails?", vbYesNo, "Send Emails")
    If Answer = vbYes Then


    Dim olLook As Object
    Dim olNewEmail As Object
    Dim distro As String
    Dim strSql As String

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb

    strSql = "SELECT * from qryEmail"


    Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

    Do While Not rst.EOF


    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.CreateItem(0)

    With olNewEmail 'Attach template
    .To = rst.Fields("[EMailAddress]")
    .SUBJECT = rst.Fields("[SUBJECT]")
    .HTMLBody = "<font size=5 pt><font color=red><b><u>PLEASE READ</font></b></u>"
    .Attachments.Add = rst.Fields("[ATTACH]")
    .SendUsingAccount = Outlook.Session.Accounts.Item(2)
    .Display
    End With

    rst.MoveNext
    Loop

    End If
    End Sub

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    try

    Code:
    set olnewmail.SendUsingAccount = Outlook.Session.Accounts.Item(2)

  5. #5
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11
    Hi Andy49 this worked I added "set olnewemail.SendUsingAccount=Outlook.Session.Accounts.Ite m(2)"

    Now the only issue I am having is making the code run with the line added ".Attachments.Add = rst.Fields("[ATTACH]") " I receive a error message stating "the operation failed".

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Quote Originally Posted by kiwikiki718 View Post
    Thanks Andy49. this helped alot, I am having to issues adding the attachments to the email ("The operation failed") also I have set the code to use a different account for sending the emails but it sends from my default email account no matter if I input "1" or "2". below is the code that I have so far:

    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you Sure you want to Send Emails?", vbYesNo, "Send Emails")
    If Answer = vbYes Then


    Dim olLook As Object
    Dim olNewEmail As Object
    Dim distro As String
    Dim strSql As String

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb

    strSql = "SELECT * from qryEmail"


    Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

    Do While Not rst.EOF


    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.CreateItem(0)
    Set myAttachments = olNewEmail.Attachments
    With olNewEmail 'Attach template
    .To = rst.Fields("[EMailAddress]")
    .SUBJECT = rst.Fields("[SUBJECT]")
    .HTMLBody = "<font size=5 pt><font color=red><b><u>PLEASE READ</font></b></u>"
    myAttachments.Add = rst.fields("[Attach]")
    .SendUsingAccount = Outlook.Session.Accounts.Item(2)
    .Display
    End With

    rst.MoveNext
    Loop

    End If
    End Sub
    These changes should do the trick

  7. #7
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11
    I receive the following error:"object doesn't not support this property or method".

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    if the issue is the second green line, try putting it directly underneath the first one

  9. #9
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11
    Hi Andy, yes the issue is with the second green line, I moved that line underneath the first one and now I am receiving a error message stating :" the operation failed"


    Dim olNewEmail As Object
    Dim strSql As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset



    Set db = CurrentDb

    strSql = "SELECT * from qryEmail"

    Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

    Do While Not rst.EOF


    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.CreateItem(0)
    Set myattachments = olNewEmail.Attachments
    myattachments.Add = rst.Fields("[ATTACH]")

    Set olNewEmail.SendUsingAccount = Outlook.Session.Accounts.Item(2)


    With olNewEmail 'Attach template
    .To = rst.Fields("[EMailAddress]")
    .SUBJECT = rst.Fields("[SUBJECT]")
    .HTMLBody = "<font size=5 pt><font color=red><b><u>PLEASE READ THE ENTIRE ATTACHED FILE.</u></b></font>"
    .SendUsingAccount = olNewEmail.SendUsingAccount
    .Display
    End With

    rst.MoveNext
    Loop

  10. #10
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Are your attachments stored on the database


    Sent from my iPhone using Tapatalk

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    You can't ask it to get myattachments.add=rst.fields(empty????)


    Sent from my iPhone using Tapatalk

  12. #12
    kiwikiki718 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    11
    Hi Andy sorry for the delay, my attach field contains the paths for the pdf For each email address . Could this be the issue?

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Then surely the line would end fields("attach")


    Sent from my iPhone using Tapatalk

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

Similar Threads

  1. Replies: 5
    Last Post: 05-07-2014, 09:25 AM
  2. Command Button to Send Outlook Email
    By AJM229 in forum Forms
    Replies: 19
    Last Post: 05-07-2014, 09:05 AM
  3. Replies: 4
    Last Post: 02-05-2014, 04:05 PM
  4. "Send Email" button on Query-Based Report
    By athyeh in forum Programming
    Replies: 5
    Last Post: 07-05-2013, 12:44 PM
  5. Create Hyperlink and send via email
    By tcheck in forum Access
    Replies: 7
    Last Post: 11-20-2012, 06:24 PM

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