Criteria for sending mail (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
Criteria for sending the mail
Hello all,
I really need your help on this,
I have created a report called calibration instruments list and first column is location,it will be automatically filtered to particular location like"production"with the help of combo box in form filter.
Now using sendobject I need to send mail automatically to related person like if my first column field is production,when I click send button it should open outlook with to "xxxx" and if it is qc then for"yyyy".
Using vba if or anything else kindly tell me how to write the code....
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:40
Joined
Aug 30, 2003
Messages
36,118
Welcome to AWF. If you can have the email address as a hidden column in the combo box, you can simply refer to there for the appropriate address.

FYI, I deleted you duplicate post. There's no need to post the same question twice.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! If you're using a combobox, you could maybe include the email address in the combo and then use it in your SendObject code.
 

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
Dear sir thank you so much for the quick reply,
Could u pls show me the syntax for the same...I am starting to learn VBA in recent days.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Dear sir thank you so much for the quick reply,
Could u pls show me the syntax for the same...I am starting to learn VBA in recent days.
To make sure you are sending the report to the correct address, I would recommend adding a Textbox on the form to show the email address from the selected department on the combobox. For example:
Code:
=[ComboboxName].[Column](1)
Then, in your SendObject code, you could simply use the name of the Textbox for the To address argument.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,169
you need to add code to the Click event of the button:
Code:
Private Sub button1_Click()
Dim sEMail As String
If [Report].[comboDepartment] = "QC"
    sEmail = "email address of qc to contact"
End If
If [Report].[comboDepartment] = "Production"
    sEmail = "email address of production manager/supervisor, etc."
End If
docmd.SendObject Objecttype:=acSendReport, _
ObjectName:="theReportHere", _
Output:=acFormatPDF,To:=sEMail, _
Subject:="your subject", _
MessageText:="your message"
End Sub
 

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
dear sir ,it is not working, it is showing to address as "sEmail, here is my code:
Private Sub cmddepthead_Click()
Dim sEMail As String
If [Report].[Location Brief] = "QC-Wires" Then
sEMail = "xxxx"
End If
If [Report].[Location Brief] = "Blk wire drawing" Then
sEMail = "uuuuu"
End If
DoCmd.SendObject acSendReport, "calibration due list", ".pdf", "sEmail", , , "Instruments due for calibration", "trial purpose"
End Sub
 

Micron

AWF VIP
Local time
Today, 08:40
Joined
Oct 20, 2018
Messages
3,476
remove the quotes around "sEmail" and try again. It represents a variable that is a string data type, and they were not used in the example given. So in this case, just
... "calibration due list", ".pdf", sEmail, , , ...
Note that I didn't examine the rest of your code for any other errors, so when you fix that, there may be something else.
 

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
dear sir one more doubt ,
if my report include both the location say "location brief" & "qc-wires", will it send to both of them ??
if not kindly suggest me a code syntax.
Thank you,in advance for your help.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,169
you need to change the sEmail string into an array:
Code:
Private Sub cmddepthead_Click()
	Dim arrEmail() as String
	Dim i As Integer

	i = 1
	If [Report].[Location Brief] = "QC-Wires" Then
		Redim Preserve arrEmail(1 To i)
		arrEmail(i)="wwwwww"
		i = i + 1
	End If
	If [Report].[Location Brief] = "Blk wire drawing" Then
		Redim Preserve arrEmail(1 To i)
		arrEmail(i)="xxxxx"
		i = i + 1
	End If
	If [Report].[Location Brief] = "location brief & qc-wires" Then
		Redim Preserve arrEmail(1 To i)
		arrEmail(i)="yyyyy" 'email for location brief
		i = i + 1
		Redim Preserve arrEmail(1 To i)
		arrEmail(i)="zzzzz" 'email for qc-wires
	End If
For i = 1 to Ubound(arrEMail)
	DoCmd.SendObject acSendReport, "calibration due list", ".pdf", arrEmail(i), , , "Instruments due for calibration", "trial purpose"
Next i
End Sub
 

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
Dear sir thank you for your help,
My Database is almost over now as I am a beginner I really appreciate your help and support.
Can I save this report automatically week on week in my desktop?
 

Ravi Kumar

Registered User.
Local time
Today, 18:10
Joined
Aug 22, 2019
Messages
162
can anyone pls tell me how to save my report automatically in xlsx format every 7 days??
 

Users who are viewing this thread

Top Bottom