Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-22-2019, 07:12 AM   #1
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Question Criteria for sending mail

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....

Ravi Kumar is offline   Reply With Quote
Old 08-22-2019, 07:33 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,313
Thanks: 13
Thanked 4,114 Times in 4,046 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Criteria for sending mail

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Ravi Kumar (08-22-2019)
Old 08-22-2019, 07:33 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,956
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Criteria for sending mail

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Ravi Kumar (08-22-2019)
Old 08-22-2019, 07:39 AM   #4
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Criteria for sending mail

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.
Ravi Kumar is offline   Reply With Quote
Old 08-22-2019, 07:42 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,956
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Criteria for sending mail

Quote:
Originally Posted by Ravi Kumar View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Ravi Kumar (08-22-2019)
Old 08-22-2019, 07:43 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Criteria for sending mail

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ravi Kumar (08-22-2019)
Old 08-22-2019, 07:24 PM   #7
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Post Re: Criteria for sending mail

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

Ravi Kumar is offline   Reply With Quote
Old 08-22-2019, 07:45 PM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,104
Thanks: 10
Thanked 221 Times in 209 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Criteria for sending mail

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Ravi Kumar (08-23-2019)
Old 08-23-2019, 02:40 AM   #9
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Criteria for sending mail

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.
Ravi Kumar is offline   Reply With Quote
Old 08-23-2019, 03:06 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Criteria for sending mail

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ravi Kumar (08-23-2019)
Old 08-23-2019, 05:13 AM   #11
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Criteria for sending mail

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 is offline   Reply With Quote
Old 08-26-2019, 04:04 AM   #12
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Criteria for sending mail

can anyone pls tell me how to save my report automatically in xlsx format every 7 days??
Ravi Kumar is offline   Reply With Quote
Old 08-26-2019, 04:16 AM   #13
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Criteria for sending mail

Have a look at this thread?

https://access-programmers.co.uk/for...d.php?t=254424

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Ravi Kumar (08-26-2019)
Reply

Tags
docmd , report , sendobject , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sending an asp.net mail wagae Web Design and Development 3 08-04-2006 04:53 AM
Sending E-mail incognito8095 Modules & VBA 1 06-23-2006 11:27 AM
Sending E-Mail vjmehra Modules & VBA 0 03-07-2005 01:42 AM
e-mail not sending CJBIRKIN Modules & VBA 7 02-13-2003 08:24 AM




All times are GMT -8. The time now is 08:23 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World