email from Database with outlook automatically populated

[ShaunG]

Hi Peoples, i have used the Macro to set up what i want and does the job well to an extent.



im trying to sus out the vba code for it, I no im off but have come up with this but im getting a compile error.

docmd.SendObject(acSendReport,rptProductstoJoinery,acFormatPDF,,,,"Process Order -","Hi" & vbNewline & vbNewLine & "See attached Order file, please process, thankyou.",true,,

if anyone could help much appreciated.

[Minty]

You have an extraneous ( .

Also rather than try and put a complex string into the command line construct the string first as a variable it makes debugging syntax much simpler - something like

Dim sMsgBody as String

sMsgBody = "Hi" & vbNewline & vbNewLine & "See attached Order file, please process, thankyou."

docmd.SendObject acReport, "rptProductstoJoinery", acFormatPdf, ,,, "Process Order ", sMsgBody, True

Note that if you include the trailing comma's in the command line you will also get errors.

Also the report Name is a string - so see how I have referred to it.

[ShaunG]

You have an extraneous ( .

Also rather than try and put a complex string into the command line construct the string first as a variable it makes debugging syntax much simpler - something like

Note that if you include the trailing comma's in the command line you will also get errors.

Also the report Name is a string - so see how I have referred to it.

thanks Minty for clearing up, is it possible to put existing signatures already saved in outlook to the message. this is on a server and used by multiple people so will have different signatures.

[ShaunG]

Minty i have adjusted the code slightly and made it universal for different users. with my sSubject string its producing the the bound column when i wont the the 2nd coloumn can i change this through code?

Private Sub Command265_Click()    On Error Resume Next
    Dim sMsgBody As String
    Dim sSubject As String
    
        sSubject = "Process Order" & " - " & Me.cboLookup7
    
        sMsgBody = "Hi," _
        & vbNewLine & vbNewLine & "See attached Order file, please process, thankyou." _
        & vbNewLine & vbNewLine & "This email is automatically generated by Access, if you are not the Real Recipient please reply to email or contact us" _
        & vbNewLine & "Cabinets" _
        & vbNewLine & "(08) "
        
        
            DoCmd.SendObject acReport, "rptOrderEmail", acFormatPDF, , , , sSubject, sMsgBody, True
            
End Sub

[Micron]

Without specifying the column, you will always retrieve the bound column, which in most cases needs to/should be the first. Try
Me.cboLookup7.Column(1) The column (and row) property of a combo or listbox is 0 based, so 1 is the second column.

[ShaunG]

Without specifying the column, you will always retrieve the bound column, which in most cases needs to/should be the first. Try
Me.cboLookup7.Column(1) The column (and row) property of a combo or listbox is 0 based, so 1 is the second column.

Micron that sorted it, thanks.

[ShaunG]

Im now trying to implement the code using Access levels works to a degree but ive got something around the wrong way, it loads outlook how i want it just doesn't put the subject and body message in now.

Private Sub Command265_Click()    On Error Resume Next
    Dim sMsgBody As String
    Dim sSubject As String
    Dim iAccLvl As Integer
    
        sSubject = "Process Order" & " - " & Me.cboLookup7.Column(1)
    
        sMsgBody = "Hi," _
            & vbNewLine & vbNewLine & "Attached is Purchase Order, can you please proceed." _
            & vbNewLine & vbNewLine & "Thankyou." _
            & vbNewLine & vbNewLine & "Regards," _
            & vbNewLine & [Forms]![LoginForm]![cboUser].[Column](1) _
            & vbNewLine & vbNewLine & "This email is automatically generated by Access, if you are not the Recipient please reply back to email or contact us." _
            & vbNewLine & "Cabinets" _
            & vbNewLine & "(08) "
    
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)




    Select Case iAccLvl
    
        Case 1, 2, 3
        
                DoCmd.SendObject acReport, "rptOrderEmail", acFormatPDF, , , , sSubject, sMsgBody, True
        Case Else
            MsgBox "You Do not have Permissions", vbOKOnly
    End Select
    


End Sub

[ShaunG]

I worked it out, trying to retrieve the user name was sending it blank and so rectified the code to this -

Private Sub Command265_Click()    On Error Resume Next
    Dim sMsgBody As String
    Dim sSubject As String
    Dim iAccLvl As Integer
    
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


    sSubject = "Process Order" & " - " & Me.cboLookup7.Column(1)
    
    sMsgBody = "Hi," _
            & vbNewLine & vbNewLine & "Attached is Purchase Order, can you please proceed." _
            & vbNewLine & vbNewLine & "Thankyou." _
            & vbNewLine & vbNewLine & "Regards," _
            & vbNewLine & vbNewLine & Forms!LoginForm!cboUser.Column(1) _
            & vbNewLine & vbNewLine & "This email is automatically generated by Access, if you are not the Recipient please reply back to email or contact us." _
            & vbNewLine & "Cabinet Tech" _
            & vbNewLine & "(08) 8447 1400"
    
    Select Case iAccLvl
    
        Case 1, 2, 3
            DoCmd.SendObject acReport, "rptOrderEmail", acFormatPDF, , , , sSubject, sMsgBody, True
        Case Else
            MsgBox "You Do not have Permissions", vbOKOnly
    End Select
    
End Sub

[Micron]

Your problem probably really comes down to your error handling. You don't say which line generated the error, but I guess it was the combo column one. By using Resume Next you might as well not have that line in there - the error is just being ignored and you move on. You only do this in special situations, such as trying to access a property that doesn't exist. Code would then create the property and then attempt to do something with it (that's only one example).

If you had a proper error handling routine, you would have discovered more about the error and then ought to correct the code or handle the error. You are doing neither as far as I can tell. Your solution might be to fix what happens if the DLookup returns an invalid value. That might be as simple as wrapping that in the Nz function, but I'm just guessing. Everybody probably has their own error handling style. This is mine

Dim statements & stuff

On error goto errHandler < tells code to go to that line label if an error occurs

Lotsa code

end of code here so must exit otherwise error handler would execute

exitHere: < this is a line label for redirect if error happens (if appropriate)
Exit sub

errHandler:
Msgbox "Error " & err.number & ": " & err.description
Resume exitHere < next line executed after this would be to exit sub

OR you write IF's for expected possible err numbers
OR you write a Case Select block to handle many expected possible err numbers

End Sub or function

[ShaunG]

thanks Micron, im not really using many error handlers as yet, will try work out how to use them.