Outlook to Gsuite (1 Viewer)

Gismo

Registered User.
Local time
Today, 14:15
Joined
Jun 12, 2017
Messages
1,298
Hi All,

just a quick one to ask for assistance. I know, my questions are never a quick one :)

When submitting a document to be mailed, the system requires to enter the gmail account password

Now for some reason the users does not enter the password, they either click on ok or cancel or on the X to close.

The macro then runs but no mail is sent.

Ok should not be active when the password has not been entered.
And when canceled, the macro should not run

1618309002260.png


Please could you advise on where the changes should be made as I am not sure

Also, if the password is not entered or incorrect, the macro should not run.

error of incorrect password should be shown then to re enter password


Option Compare Database
Option Explicit
Dim strFilename As String, strMsg As String
Dim aProjectLeaderMail As String
Dim aProductionPlannerMail As String
Dim aCurrentUserMail As String
Dim scc As String
Dim vRecipientList As String
Dim vRecipientListFrom As String


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

'Enter Password
If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
Dim Message As String, Title As String, Default As String
Message = "Enter Windows Password"
Title = "Enter Parameters"
'WPassStr = InputBox(Message, Title)
WPassStr = InputBoxDK(Message, Title) '(* Password)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE GMailSettingsQry SET WPass =""" & WPassStr & """"

End If

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailTBLQry_NewDaw; ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
'Set To Recipient
If Not IsNull(rs!To) Then vRecipientList = vRecipientList & rs("To") & ","
If Not IsNull(rs!To) Then vRecipientListFrom = vRecipientListFrom & rs("CurrentUserMail")

'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!CurrentUserMail) And rs!CurrentUserMail <> "N/A" Then aCurrentUserMail = aCurrentUserMail & rs("CurrentUserMail") & ","
'It send mail, I need Production Planner, Project leader and Current user mail (If not the same as production planner)
If aProjectLeaderMail <> aProductionPlannerMail Then
scc = scc & aProjectLeaderMail & "," & aProductionPlannerMail & ","
If aCurrentUserMail <> aProductionPlannerMail And aCurrentUserMail <> aProjectLeaderMail Then
scc = scc & "," & aCurrentUserMail & ","
End If
Else
scc = scc & aProjectLeaderMail & ","
If aCurrentUserMail <> aProductionPlannerMail Then
scc = scc & "," & aCurrentUserMail & ","
End If
End If

rs.MoveNext


Loop Until rs.EOF


'vSubject = "New DAW Sheet Listing - Registration: " & " " & rs("Registration")
vSubject = "New DAW Sheet Listing - Registration: " & " " & DLookup("[Registration]", "[EmailTblQry_NewDaw]")
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 report as attachment>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SendEMailCDO vRecipientList, scc, vSubject, vMsg, "", vReportPDF
'<<<<<<<<<<<<<<<<<<call Colin's sub to email report as attachment>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'MsgBox ("Report successfully eMailed!")

'Debug.Print aProjectLeaderMail, aProductionPlannerMail, aCurrentUserMail
'Debug.Print sCC
'Debug.Print vRecipientList
'Debug.Print aCurrentUserMail

Else
MsgBox "No contacts."
End If
DoCmd.RunMacro "New DAW Email List"
DoCmd.SetWarnings True
End Sub
 

bastanu

AWF VIP
Local time
Today, 05:15
Joined
Apr 13, 2010
Messages
1,401
Because you are using the built in InputBox (indirectly via the modified InputBoxDK that allows the masking) you cannot easily disable the OK button. To do that you would need to replace it with your own modal form.
To exit the code if the user does not enter a password simply add this line after the call to the inputbox:
Code:
WPassStr = InputBoxDK(Message, Title) '(* Password)
If WPassStr ="" Then Msgbox "You did not enter a password, the emailing process will end",vbCritical :Exit Sub

To check if the password is correct you would need to either store it in the database (which would obviously pose certain risks) or wait until you get the Gmail authentication error. What happens now if the wrong password is entered?

Cheers,
 

Gismo

Registered User.
Local time
Today, 14:15
Joined
Jun 12, 2017
Messages
1,298
Because you are using the built in InputBox (indirectly via the modified InputBoxDK that allows the masking) you cannot easily disable the OK button. To do that you would need to replace it with your own modal form.
To exit the code if the user does not enter a password simply add this line after the call to the inputbox:
Code:
WPassStr = InputBoxDK(Message, Title) '(* Password)
If WPassStr ="" Then Msgbox "You did not enter a password, the emailing process will end",vbCritical :Exit Sub

To check if the password is correct you would need to either store it in the database (which would obviously pose certain risks) or wait until you get the Gmail authentication error. What happens now if the wrong password is entered?

Cheers,
I was thinking, maybe I should convert the inputbox to a form.

When an incorrect password, no password or cancel, the macro runs, no mail is sent and the form closes and operation continues

I need to have the password entered, authenticated, then to continue to the macro.
 

bastanu

AWF VIP
Local time
Today, 05:15
Joined
Apr 13, 2010
Messages
1,401
Are you going to authenticate the password yourself by comparing it with a stored value in your db (which could expose security risks) or letting Google reject the login attempt and catching that. I asked in my previous post what happens if you enter a wrong password, can you please let us know?
 

Gismo

Registered User.
Local time
Today, 14:15
Joined
Jun 12, 2017
Messages
1,298
I would rather let Google authenticate and reject
 

bastanu

AWF VIP
Local time
Today, 05:15
Joined
Apr 13, 2010
Messages
1,401
And it does not? Can you show what is happening if you enter the wrong password? What message\error do you get?

Cheers,
Vlad
 

Gismo

Registered User.
Local time
Today, 14:15
Joined
Jun 12, 2017
Messages
1,298
And it does not? Can you show what is happening if you enter the wrong password? What message\error do you get?

Cheers,
Vlad
It does indeed, I get error message

1618461461209.png


and when you had an error, the hour meter remains running
also, when you had an error and the hour meter runs, it is difficult to navigate to design mode

I just need to be able to go back to re enter the password.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:15
Joined
Jul 9, 2003
Messages
16,244
Have a look at the Gmail code on my website here.


I draw your attention to the error checking code block, in particular it checks that error code your error has generated, returning a more helpful error message. "Wrong Password"...

You might benefit from incorporating at error block into your project....
 
Last edited:

Gismo

Registered User.
Local time
Today, 14:15
Joined
Jun 12, 2017
Messages
1,298
Have a look at the Gmail code on my website here.

I draw your attention to the error checking code block, in particular it checks that error code your error has generated, returning a more helpful error message. "Wrong Password"...

You might benefit from incorporating at error block into your project....
Thank you

Will most definitely have a look.

Please supply URL to you Website
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:15
Joined
Jul 9, 2003
Messages
16,244
Please supply URL to you Website

Opp's!

Here:-

 

Users who are viewing this thread

Top Bottom