email

[bastanu]

 

[Gismo]

I am getting the following error and not sure where to look for the origin

And remember the "To" field we removed, this was for the managers email, now not included anymore

 

[bastanu]

Put a break on the .Send 'Send the message line and inspect the aTo,aCC and aFrom variables.

Cheers,

 

[isladogs]

I am getting the following error and not sure where to look for the origin

And remember the "To" field we removed, this was for the managers email, now not included anymore

View attachment 99906

That message means the senders email address is missing.
For a full list of CDO error messages, see my help file attached

 

[The_Doc_Man]

Earlier you asked why something was happening related to the DO and LOOP error.

This "DO without LOOP" error (or any similar error where it says you started but didn't end a loop) can be caused by improperly terminated "IF/THEN/ELSE/END IF" or "DO/LOOP" combinations. This isn't widely discussed in these forums, or at least I haven't seen it come up lately, but there is a thing called an "implied code block" that is delimited by syntax that starts something and expects to eventually find syntax that will end it. These "implied code blocks" have the property that you cannot jump into the middle of them. Which usually means that for your DO loop, you probably have an improperly terminated "IF" block inside of the "DO" block. What happens specifically is that VBA's compiler thinks the LOOP statement (which appears to VBA to be inside the bad IF block) is trying to jump to another place that is in the middle of a different code block.

Therefore, the REAL error is probably an unterminated IF block, but the DO's LOOP occurs first and THAT is what stops the compilation. Stated another way, the compiler hit the LOOP statement while still looking for an END IF.

 

[bastanu]

@The_Doc_Man - excellent explanation, maybe you could post it in one of the tutorial or code sub-forums.
Unfortunately in this particular case the actual Loop was missing.....

Cheers,

 

[Gismo]

Thank you all for the assistance

Seems to be working well

 

[Gismo]

One las question on this
After I have sent a mail, some of the functions in access seems disabled
I can not Click on view and select display or what I found now was I was editing a form and i wanted the make the underlying query as sum and sum was not available

Is there any function I need to turn on off off after sending email?

 

[bastanu]

I had another look at the code provided and I cannot see anything in there (like some unhandled Docmd.Echo calls) that could affect Access functionality. So I don't think those issues are related to the emailing, could be some corruption creeping in. You should try to decompile the project, run a compact and repair then try to compile it; you need to fix any compile errors showing up at this step then run the second compact and repair.

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

https://www.barnesbusinesssolutions.com/WhitePaperOnHowAndWhyToDecompileAnAccessDatabase.pdf

Cheers,

 

[Gismo]

Hi,

When I mail the applicable PDF reports, the subject line gives a report name description, the registration and the sequence number of the record
In the case where more than one report is mailed, I would like to have the description to display all the sequence numbers which I get from a separate qry "EmailTBLQry_NewDaw_Sequence_Sum"

I am not 100% sure how to do the loop on this.
Please could you advise

Private Sub Excecute_Click()
Dim SQL As String
Dim WPassStr As String
Dim sSQL As String

'Check Gmail Settings
If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
DoCmd.OpenForm "Airbus Mail Frm - New DAW"
Exit Sub
End If

'Enter Password
If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete blank Entries - DAW Status", acViewNormal, acEdit
DoCmd.OpenQuery "Clear_Registration_File", acViewNormal, acEdit
DoCmd.OpenQuery "Update_Registration_All", acViewNormal, acEdit
DoCmd.SetWarnings True
Exit Sub
End If

Dim rs As Recordset
Dim rss As Recordset
Dim vMsg As String
Dim vSubject As String
Dim vReportPDF As String

'Obtain the email address for the current user email - "From" email
vRecipientListFrom = Nz(DLookup("CurrentUserMail", "[GMailSettingsQry]")) 'Vlad:this line replaces the entire commented out block below
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<NOT NEEDED - REMOVE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Set rss = CurrentDb.OpenRecordset("SELECT * FROM GMailSettingsQry; ")
'If rss.RecordCount > 0 Then
'rss.MoveFirst
''Do Until rss.EOF 'Vlad - makes it easier to follow to code -commented out as this has only one record so no need to loop
'If Not IsNull(rss!CurrentUserMail) Then vRecipientListFrom = vRecipientListFrom & rss("CurrentUserMail")
''vRecipientListFrom is not used anywhere, I think it should be in this line:SendEMailCDO vRecipientList, scc, vSubject, vMsg, "", vReportPDF
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<NOT NEEDED - REMOVE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



'Obtain all the cc addresses
Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailTBLQry_NewDaw; ")
If rs.RecordCount > 0 Then rs.MoveFirst

Do Until rs.EOF

'Set To Recipient
If Not IsNull(rs!To) Then vRecipientList = vRecipientList & rs("To") & ","

'Set CC Recipient
If Not IsNull(rs!ProjectLeaderMail) And rs!ProjectLeaderMail <> "N/A" Then aProjectLeaderMail = aProjectLeaderMail & rs("ProjectLeaderMail") & ","
If Not IsNull(rs!ProductionPlannerMail) And rs!ProductionPlannerMail <> "N/A" Then aProductionPlannerMail = aProductionPlannerMail & rs("ProductionPlannerMail") & ","
If Not IsNull(rs!MaterialPlannerMail) And rs!MaterialPlannerMail <> "N/A" Then aMaterialPlannerMail = aMaterialPlannerMail & rs("MaterialPlannerMail") & ","
If Not IsNull(rs!CurrentUserMail) And rs!CurrentUserMail <> "N/A" Then aCurrentUserMail = aCurrentUserMail & rs("CurrentUserMail") & ","

If aProjectLeaderMail <> aProductionPlannerMail Then
scc = scc & aProjectLeaderMail & "," & aProductionPlannerMail & ","
Else
scc = scc & "," & aProjectLeaderMail & "," 'Vlad: you had this here but also below: & aCurrentUserMail & ","
End If
If aCurrentUserMail <> aProductionPlannerMail And aCurrentUserMail <> aProjectLeaderMail Then
scc = scc & "," & aCurrentUserMail & ","
End If
'Vlad:add MaterialPlannerMail
If InStr(scc, aMaterialPlannerMail) = 0 Then scc = scc & "," & aMaterialPlannerMail & ","

rs.MoveNext
Loop

vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & DLookup("[DawNoSeq]", "[EmailTBLQry_NewDaw_Sequence_Sum]")
vReportPDF = CurrentProject.Path & "\" & "DAW Sheet.pdf"

'<<<<<<<<<<<<<<<<<<<<export the report as PDF>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DoCmd.OutputTo acReport, "DAW Sheet", acFormatPDF, vReportPDF
'<<<<<<<<<<<<<<<<<<<<export the report as PDF>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


'<<<<<<<<<<<<<<<<<<call Colin's sub to email reportio as attachment>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'SendEMailCDO vRecipientList, scc, vSubject, vMsg, "", vReportPDF 'Vlad:you get the From in this sub in vRecipientListFrom variable so use it
SendEMailCDO vRecipientList, scc, vSubject, vMsg, vRecipientListFrom, vReportPDF
'<<<<<<<<<<<<<<<<<<call Colin's sub to email report as attachment>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Vlad:cleanup recordset
rs.Close
Set rs = Nothing

'MsgBox ("Report successfully eMailed!")

'Debug.Print aProjectLeaderMail, aProductionPlannerMail, aCurrentUserMail, aMaterialPlannerMail
'Debug.Print sCC
'Debug.Print vRecipientList
'Debug.Print vRecipientListFrom
'Debug.Print aCurrentUserMail
'Debug.Print DAWNo

'Else
'MsgBox "No contacts."
'End If
End Sub

 

[bastanu]

The easiest way if the new query does not have any parameters is to use a concatenation function such as theDbGuy's SimpleCSV: http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv

Just copy it into a standard module and use it in your code like this:
vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & SimpleCSV("Select DawNoSeq FROM EmailTBLQry_NewDaw_Sequence_Sum;")

If your query has parameters this will probably fail with the "Too few parameters" error. In that case you can use another recordset based on that query. Please post back what you get.

Cheers,

 

[Gismo]

The easiest way if the new query does not have any parameters is to use a concatenation function such as theDbGuy's SimpleCSV: http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv

Just copy it into a standard module and use it in your code like this:
vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & SimpleCSV("Select DawNoSeq FROM EmailTBLQry_NewDaw_Sequence_Sum;")

If your query has parameters this will probably fail with the "Too few parameters" error. In that case you can use another recordset based on that query. Please post back what you get.

Cheers,

Hi,

How will I then adapt my code to reference to SimpleCSV records when populating the VSubject?

I copied the SimpleCSV code to a module as well

vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & DLookup("[DawNoSeq]", "[EmailTBLQry_NewDaw_Sequence_Sum]")

I need to remove the "[EmailTBLQry_NewDaw_Sequence_Sum]" part and reference SimpleCSV corrects?

vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & DLookup("[DawNoSeq]", SimpleCSV)
SimpleCSV ("Select DawNoSeq FROM EmailTBLQry_NewDaw_Sequence_Sum;")
vReportPDF = CurrentProject.Path & "\" & "DAW Sheet.pdf"

 

[bastanu]

I gave it to you in my previous email:

vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & SimpleCSV("Select DawNoSeq FROM EmailTBLQry_NewDaw_Sequence_Sum;")

You replace the last dLookup with the SimpleCSV.

Cheers,

 

[Gismo]

I gave it to you in my previous email:

vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]") & " " & ", DAW Sheet" & " " & SimpleCSV("Select DawNoSeq FROM EmailTBLQry_NewDaw_Sequence_Sum;")

You replace the last dLookup with the SimpleCSV.

Cheers,

Works like a charm
Thank you very much

 

[bastanu]