Sending Emails and VBA coding

[lucy1216]

First of all, to whomever reads and/or responds I thank you for your patience.
I am using Access 2010/Outlook 2010 32bit
I am relatively new to VBA and I am completely lost on how to accomplish the following:
My database was built to facilitate approvals for accounts payable.
My issue now is that I want to create a command button that sends out reminders using outlook.
To make it simplistic, here is what I have:
tblReceiveTemp-This table holds the following fields:
ID-Key, Level 1Approver, Level 1Approver Email, Status, ATT, Sprint, USA, Verizon, ATTReceiveStatus, SprintReceiveStatus, USAReceiveStatus, VerizonReceiveStatus
ReminderOne = rptReminderOne


ReminderTwo=rptReminderTwo
ReminderThree=rptReminderThree
tblEmailOutgoing-This table holds the following fields:
ID-Key, ApproverName, ATTNotificationBill, ATTReminderOne, ATTReminderTwo, ATTReminderThree, SprintNotificationBill, SprintReminderOne, SprintReminderTwo, SprintReminderThree, USANotificationBill, USAReminderOne, USAReminderTwo, USAReminderThree, VerizonNotificationBill, VerizonReminderOne, VerizonReminderTwo, VerizonReminderThree, COBDate
qryATTNotReceived –
SELECT tblReceiveTemp.ATTReceiveStatus, tblReceiveTemp.[Level 1Approver Email] AS Email, tblReceiveTemp.[Level 1Approver]
FROM tblReceiveTemp
WHERE (((tblReceiveTemp.ATTReceiveStatus)="NR"));
qrySprintNotReceived –
SELECT tblReceiveTemp.SprintReceiveStatus, tblReceiveTemp.[Level 1Approver Email] AS Email, tblReceiveTemp.[Level 1Approver]
FROM tblReceiveTemp
WHERE (((tblReceiveTemp.SprintReceiveStatus)="NR"));
qryUSANotReceived-
SELECT tblReceiveTemp.USAReceiveStatus, tblReceiveTemp.[Level 1Approver Email] AS Email, tblReceiveTemp.[Level 1Approver]
FROM tblReceiveTemp
WHERE (((tblReceiveTemp.USAReceiveStatus)="NR"));
qryVerizonNotReceived-
SELECT tblReceiveTemp.VerizonReceiveStatus, tblReceiveTemp.[Level 1Approver Email] AS Email, tblReceiveTemp.[Level 1Approver]
FROM tblReceiveTemp
WHERE (((tblReceiveTemp.VerizonReceiveStatus)="NR"));

So here is basically what I want to do and I need a lot of assistance with this code:

Reminder One:

If tblReceiveTemp.’Vendor’ReciveStatus, = “NR”
THEN sendobject “rptReminderOne” to all persons in qry’Vendor’NotReceived until EOF
Update tblEmailOutgoing.’Vendor”ReminderOne = Now()

**’Vendor’ is a placeholder for ATT, Sprint and Verizon.

Reminder Two:

If tblReceiveTemp.ATTReceiveStatus = “NR” and tlbEmailOutgoing.ReminderOne is not null
Then sendobject “rptReminderTwo” to all persons in qry’Vendor’NotReceived until EOF
Update tblEmailOutgoing.’Vendor’ReminderTwo = Now()

Reminder Three:
If tblReceiveTemp.ATTReceiveStatus = “NR” and tlbEmailOutgoing.ReminderTwo is not null
Then sendobject “rptReminderThree” to all persons in qry’Vendor’NotReceived until EOF
Update tblEmailOutgoing.’Vendor’ReminderThree = Now()

Basically all I need assistance with is setting up the code to send the emails. In addition, I need to verify that I have all of the correct references check in the code window.

Thanks again.

[June7]

Not much of that makes sense to me.

Sending emails with VBA is common topic in forum.
Start with https://www.accessforums.net/program...ook-21903.html

When you have some code with specific issue, post for analysis.

[sdel_nevo]

Hi There

this is the code that i have on the On Click event of a button, that e-mails a report "Invoice" to a client

Private Sub E_Mail_Invoice_Click()


'This Command Opens the report in hidden mode so that the calculations work on the Report when it's output as a PDF
DoCmd.OpenReport "Invoice", acViewPreview, "", "", acHidden
'Filepath is the path to the report thats put into the Company Details form
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, DLookup("FilePath", "Company Details", "CompanyID = 1") & "\Invoice.pdf"


Dim OlApp As Object
Dim objMail As Object


On Error Resume Next 'Keep going if there is an error
'See if Outlook is open
Set OlApp = GetObject(, "Outlook.Application")
If Err Then 'Outlook is not open
'Create a new instance of Outlook
Set OlApp = CreateObject("Outlook.Application")
End If


'Create e-mail item
Set objMail = OlApp.CreateItem(olMailItem)


'Set objOutlookAttach = .Attachments.Add("Filepath from Company Form")
With objMail


'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Me.EMail.Value
.Subject = "Forest PC Repairs Invoice Attached"
.HTMLBody = "Dear" & " " & Me.Full_Name.Value & " " & Me.Email_Body_text.Value
.Attachments.Add DLookup("FilePath", "Company Details", "CompanyID = 1") & "\Invoice.pdf"


End With
DoCmd.Close acReport, "Invoice", acSaveYes




End Sub

the report "Invoice" is built from a query, getting access to e-mail reports is quite straightforward you should be able to alter this code to suit your needs.
please be aware that DLookup("FilePath", "Company Details", "CompanyID = 1") & "\Invoice.pdf" and "Dear" & " " & Me.Full_Name.Value & " " & Me.Email_Body_text.Value would need to be changed to suit your needs.

once you get the report you require I.E what you want to send to your end users "an Invoice" for example sending it is the easy part.

hope this helps you

Steve

[redbull]

Another example, pretty basic.. Just make you have the outlook reference library enabled.

Sub CreateHTMLMail()
        ''''Creates a new e-mail item and modifies its properties'


    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    'Create e-mail item'
    Set objMail = olApp.CreateItem(olMailItem)


On Error GoTo MailErr


          ''' Assigning things above in outlook mail message.
         With objMail
        .BodyFormat = 3
        .Subject = 
        .Body = "
        .To = 
        .Display
        .ReadReceiptRequested = True
        
                
      End With
      Exit Sub
MailErr:
   MsgBox "Unable to send email" err.number & err.description
   
  End Sub

[lucy1216]

Thank you all...I finally got my code to work, just working on sending a .txt file as the body of the email.