To Field is not populating when sending email from Access

**HansJ** · 05-20-2016, 01:45 PM

I found this code and it works well for my application, except the "To:". After the olItem.To = I can enter "any thing I wish" and it shows what I had typed. But when I try to reference a text box in the report that houses the email addresses then the "To:" is blank. Does anyone have an idea?

Code:

Private Sub btnEmailVoucher_Click()
'Public Sub NewEmail()
    Dim olApp As Object
    Dim olItem As Variant
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strQry As String
    Dim aHead(1 To 8) As String
    Dim aRow(1 To 8) As String
    Dim aBody() As String
    Dim lCnt As Long
    Dim eRow As String
   
    
    'Create the header row
    aHead(1) = "Visit Date"
    aHead(2) = "Invoice Date"
    aHead(3) = "Store #"
    aHead(4) = "City, State"
    aHead(5) = "Hours"
    aHead(6) = "Rate %"
    aHead(7) = "Piece Rate Total"
    aHead(8) = "Breakdown"
    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = Me.First1 & "," & "<br>" & "If you earned Incentive or Premium Pay this pay period as defined in the Assembly Compensation Memo, due to timing of this communication, it will not be reflected on your payroll voucher below; but can be seen in Oracle on your applicable pay slip." & "<br>" & "<br>" & "<i>" & "If you have questions regarding this voucher ONLY, please reply to this message, or call (111)111-1111. Please note, a response will NOT be provided for questions unrelated to this voucher." & "<br>" & "For questions regarding mileage, piece rate, incentive pay/bonuses, Direct Deposit, etc., please contact your manager, or refer to your Assembly Manual." & "</i>" & "<br>" & "<br>" & "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"
    
 
 
 
    'Create each body row
    strQry = "SELECT * From Query1"
    Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset(strQry)
    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            lCnt = lCnt + 1
            ReDim Preserve aBody(1 To lCnt)
            aRow(1) = rec("Visit Date")
            aRow(2) = rec("Invoice Date")
            aRow(3) = rec("Store #")
            aRow(4) = rec("City, ST")
            aRow(5) = rec("SumOfHours")
            aRow(6) = Format(rec("Rate %"), "#%")
            aRow(7) = Format(rec("Piece Rate Total"), "Currency")
            aRow(8) = Format(rec("WO Invoice"), "Currency")
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If
    aBody(lCnt) = aBody(lCnt) & "</table></body></html>"
                
    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)
            
            
    olItem.display
    olItem.To = Me.txtEmail
    olItem.CC = ""
    olItem.Subject = "Assembly Piece Pay Voucher Statement - " & Date
    olItem.htmlbody = Join(aBody, vbNewLine)
    olItem.display
End Sub

**pbaldy** · 05-20-2016, 05:37 PM

I would expect that to work. Maybe take the first instance of this out:

olItem.display

I assume there's a valid email address in the textbox?

**HansJ** · 05-23-2016, 05:50 AM

pbaldy I tried your suggestion and no joy

What I did on a whim was added it to the "aRow" data then referenced that in the email and it works. However it now is in the table (in the body of the email), which I do not want. Any additional ideas? Have this to resolve and one more then this puppy will be ready to go.

**pbaldy** · 05-23-2016, 08:23 AM

I've had instances where I couldn't use the form value directly. Try declaring a string variable, put the form value in it and use it for the "to".

**HansJ** · 05-23-2016, 02:20 PM

I had some additional work to be completed and finished that... now I am having a whole new issue; "Run-Time Error 3061". The highlighted line is: Set rec = CurrentDb.OpenRecordset(strQry)
Explain what I am trying to do.
Form: "Employee Voucher" has a combobox "Combo0" that the user will select a persons name. Then they click Command Button 3 to run the Query and View the Report.

Code:

DoCmd.OpenQuery "Query2", acViewNormal, acEdit
DoCmd.OpenReport "Voucher", acViewReport, , , acWindowNormal

Query: "Query2" is a simple query to look up the information to pass to the Report

Code:

SELECT Oracle.[Resource Name], Oracle.[WO Invoice], Oracle.[Piece Rate Total], Oracle.[Rate %], Oracle.Hours, Oracle.[Avg Hourly rate], Oracle.[Visit Date], Oracle.[Invoice Date], Oracle.[Employee ID], Oracle.[Store ID], Stores.[City, ST], tblRoster.Email AS AssemEmail, Right(Trim([Assembler]),Len(Trim([Assembler]))-InStr(1,[Assembler]," ")) AS [First]
FROM tblRoster INNER JOIN (Stores INNER JOIN Oracle ON Stores.[Store #] = Oracle.[Store ID]) ON tblRoster.EmployeeID = Oracle.[Employee ID]
WHERE (((Oracle.[Employee ID])=[Forms]![Form1]![Combo0]));

Report:"Voucher" Displays the aforementioned query, and where I click the command button to execute the email. The line I highlighted in Red is where the error is occuring

[CODE][Private Sub Command33_Click()
'Public Sub NewEmail()
Dim olApp As Object
Dim olItem As Variant
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strQry As String
Dim aHead(1 To 8) As String
Dim aRow(1 To 8) As String
Dim aBody() As String
Dim lCnt As Long
Dim eRow As String

'Create the header row
aHead(1) = "Visit Date"
aHead(2) = "Invoice Date"
aHead(3) = "Store ID"
aHead(4) = "City, ST"
aHead(5) = "Hours"
aHead(6) = "Rate %"
aHead(7) = "Piece Rate Total"
aHead(8) = "WO Invoice"
lCnt = 1
ReDim aBody(1 To lCnt)
aBody(lCnt) = Me.First & "," & "<br>" & "If you earned Incentive or Premium Pay this pay period as defined in the Assembly Compensation Memo, due to timing of this communication, it will not be reflected on your payroll voucher below; but can be seen in Oracle on your applicable pay slip." & "<br>" & "<br>" & "<i>" & "If you have questions regarding this voucher ONLY, please reply to this message, or call (111)111-1111. Please note, a response will NOT be provided for questions unrelated to this voucher." & "<br>" & "For questions regarding mileage, piece rate, incentive pay/bonuses, Direct Deposit, etc., please contact your manager, or refer to your Assembly Manual." & "</i>" & "<br>" & "<br>" & "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"

'Create each body row
strQry = "SELECT * From Query2"
Set db = CurrentDb
Set rec = CurrentDb.OpenRecordset(strQry)
If Not (rec.BOF And rec.EOF) Then
Do While Not rec.EOF
lCnt = lCnt + 1
ReDim Preserve aBody(1 To lCnt)
aRow(1) = rec("Visit Date")
aRow(2) = rec("Invoice Date")
aRow(3) = rec("Store ID")
aRow(4) = rec("City, ST")
aRow(5) = rec("Hours")
aRow(6) = Format(rec("Rate %"), "#%")
aRow(7) = Format(rec("Piece Rate Total"), "Currency")
aRow(8) = Format(rec("WO Invoice"), "Currency")
aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
rec.MoveNext
Loop
End If
aBody(lCnt) = aBody(lCnt) & "</table></body></html>"

'create the email
Set olApp = CreateObject("Outlook.application")
Set olItem = olApp.CreateItem(0)

'olItem.display
olItem.To = [Voucher].[AssemEmail]
olItem.CC = ""
olItem.Subject = "Assembly Piece Pay Voucher Statement - " & Date
olItem.htmlbody = Join(aBody, vbNewLine)
olItem.display
End Sub
/CODE]

When I view the Query there are records, as well as in the Report. So I cannot figure out why it states the "Run-Time Error 3061: Too few parameters. Expected 1"
Appreciate your help/input

**pbaldy** · 05-23-2016, 02:42 PM

OpenRecordset can't resolve form references within the query. Try wrapping it in the Eval() function:

Eval('[Forms]![Form1]![Combo0]')

**HansJ** · 05-24-2016, 08:37 AM

Paul I will be honest I have no idea of where you are saying to try the eval() function. I am quite the newbie here. Any additional help is appreciated!

**pbaldy** · 05-24-2016, 09:38 AM

Copy/paste what I typed and replace the existing form reference in the query.

**HansJ** · 05-24-2016, 10:10 AM

Paul... Changed the Where from WHERE (((Oracle.[Employee ID])=[Forms]![Form1]![Combo0])); to WHERE (((Oracle.[Employee ID])=Eval([Forms]![Form1]![Combo0]))); and I still get the same error

. Should I just try and approach this from another angle? Not sure what it would be, but...

**pbaldy** · 05-24-2016, 10:31 AM

You missed that I had quotes around the form reference. You can also use a QueryDef to set the parameter, but you'd probably find it more difficult.

**HansJ** · 05-24-2016, 11:02 AM

Paul... yep missed the ' '! Works like a charm. I thank you for your assistance

**pbaldy** · 05-24-2016, 11:15 AM

Happy to help!

To Field is not populating when sending email from Access

Thread Tools

To Field is not populating when sending email from Access

Similar Threads

Sending an Email containing a link to access

Sending an Email using Chrome from Access

sending email through Access

Access sending email attachments

sending gmail email through access

Posting Permissions