Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-10-2019, 12:24 PM   #31
StuartG
Newly Registered User
 
Join Date: Sep 2018
Location: United Kingdom
Posts: 73
Thanks: 7
Thanked 0 Times in 0 Posts
StuartG is on a distinguished road
Re: Access creating Emails from stored data..

Quote:
Originally Posted by theDBguy View Post
Hi Stuart. We're done with the code from Daniel. You just save it and put it away. From this point on, we just use it wherever we need to send an email. So, how were you initiating an email message to begin with? If you were using a form, for example, then you'll need to place code behind that form to call the function to send an email. For instance, you could add a button to initiate the email message.
Oh Ok, i thought that was the code that was actually operating Outlook

Well i know you mentioned previously a template would be better however after speaking with colleagues at work they would prefer it to be pre filled text (memo Style, or like blank new email)

I was dabbling in other codes at wrote this yesterday, though it was convoluted and cumbersome but gave me the output i wanted.

[code]
Public Function SendEmail2()

Dim varName As Variant
Dim varCC As Variant
Dim varSubject As Variant
Dim varBody1 As Variant
Dim varBody2 As Variant


varName = ""
varCC = ""
'separate each email by a ','

varSubject = "Acknowledgement Of Receipt"
'Email subject

varBody1 = "Dear" & Chr(10) & "" & Chr(10) & "Thank you for your purchase order." & Chr(10) & "" & Chr(10) & "Customer Purchase Order Reference:" & Chr(9) & ":-" & Chr(10) & "Product Line Ordered:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Quote Reference:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Total Order Value:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "" & Chr(10) & ""
varBody2 = "Please note that this is only an acknowledgement of your receipt of your order and your contract to purchase these items is not complete until we send you an order confirmation" & Chr(10) & "" & Chr(10) & "Attached is a copy of the Thermo Fisher Scientific General Terms and Conditions of Sale July 2010, which will apply to this order" & Chr(10) & "" & Chr(10) & "Please be aware that deliveries will be arranged to a ground floor warehouse/goods in, unless previously discussed and agreed with Thermo Fisher Scientific Area Account manager who coordinated this instrument purchase" & Chr(10) & "" & Chr(10) & "If a non-standard delivery is required (e.g. to a 1st floor laboratory) and is not included in the shipping of this instrument, please contact us and we can arrange a quotation from a specialist courier for you" & Chr(10) & "" & Chr(10) & "If you have any questions in the meantime please do not hesitate to contact us." & Chr(10) & "" & Chr(10) & "Kind Regards"




'Body of the email
DoCmd.SendObject , , , varName, varCC, , varSubject, varBody1 + varBody2, True, False
'Send email command. The True after "varBody" allows user to edit email before sending.
'The False at the end will not send it as a Template File

End Function

i attach a picture of the end result, this is how my colleaues would prefer the output, however when they click that at the window opens it needs to include an attachment.

Hope i haven't confused things?
Attached Images
File Type: jpg Code output.jpg (96.1 KB, 26 views)

StuartG is offline   Reply With Quote
Old 01-10-2019, 07:27 PM   #32
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,131
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Quote:
Originally Posted by StuartG View Post
Oh Ok, i thought that was the code that was actually operating Outlook
Yes, it was the one "automating" Outlook. All we need to do is pass the things it needs/requests to in-turn pass to Outlook to generate the email message.


Quote:
I was dabbling in other codes at wrote this yesterday, though it was convoluted and cumbersome but gave me the output i wanted.
This is good. We just need to modify it to use the new function now. For example, you could try:


Code:
Public Function SendEmail2()

Dim varName As Variant
Dim varCC As Variant
Dim varSubject As Variant
Dim varBody1 As Variant
Dim varBody2 As Variant


varName = ""
varCC = ""
'separate each email by a ','

varSubject = "Acknowledgement Of Receipt"
'Email subject

varBody1 = "Dear" & Chr(10) & "" & Chr(10) & "Thank you for your purchase order." & Chr(10) & "" & Chr(10) & "Customer Purchase Order Reference:" & Chr(9) & ":-" & Chr(10) & "Product Line Ordered:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Quote Reference:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Total Order Value:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "" & Chr(10) & ""
varBody2 = "Please note that this is only an acknowledgement of your receipt of your order and your contract to purchase these items is not complete until we send you an order confirmation" & Chr(10) & "" & Chr(10) & "Attached is a copy of the Thermo Fisher Scientific General Terms and Conditions of Sale July 2010, which will apply to this order" & Chr(10) & "" & Chr(10) & "Please be aware that deliveries will be arranged to a ground floor warehouse/goods in, unless previously discussed and agreed with Thermo Fisher Scientific Area Account manager who coordinated this instrument purchase" & Chr(10) & "" & Chr(10) & "If a non-standard delivery is required (e.g. to a 1st floor laboratory) and is not included in the shipping of this instrument, please contact us and we can arrange a quotation from a specialist courier for you" & Chr(10) & "" & Chr(10) & "If you have any questions in the meantime please do not hesitate to contact us." & Chr(10) & "" & Chr(10) & "Kind Regards"




 'Body of the email


SendEmail varName, varSubject, varBody1 & varBody2, True


'Send email command. The True after "varBody" allows user to edit email before sending.
 'The False at the end will not send it as a Template File

End Function
__________________
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
Old 01-11-2019, 12:30 AM   #33
StuartG
Newly Registered User
 
Join Date: Sep 2018
Location: United Kingdom
Posts: 73
Thanks: 7
Thanked 0 Times in 0 Posts
StuartG is on a distinguished road
Re: Access creating Emails from stored data..

Quote:
Originally Posted by theDBguy View Post
Yes, it was the one "automating" Outlook. All we need to do is pass the things it needs/requests to in-turn pass to Outlook to generate the email message.


This is good. We just need to modify it to use the new function now. For example, you could try:


Code:
Public Function SendEmail2()

Dim varName As Variant
Dim varCC As Variant
Dim varSubject As Variant
Dim varBody1 As Variant
Dim varBody2 As Variant


varName = ""
varCC = ""
'separate each email by a ','

varSubject = "Acknowledgement Of Receipt"
'Email subject

varBody1 = "Dear" & Chr(10) & "" & Chr(10) & "Thank you for your purchase order." & Chr(10) & "" & Chr(10) & "Customer Purchase Order Reference:" & Chr(9) & ":-" & Chr(10) & "Product Line Ordered:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Quote Reference:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "Total Order Value:" & Chr(9) & "" & Chr(9) & "" & Chr(9) & ":-" & Chr(10) & "" & Chr(10) & ""
varBody2 = "Please note that this is only an acknowledgement of your receipt of your order and your contract to purchase these items is not complete until we send you an order confirmation" & Chr(10) & "" & Chr(10) & "Attached is a copy of the Thermo Fisher Scientific General Terms and Conditions of Sale July 2010, which will apply to this order" & Chr(10) & "" & Chr(10) & "Please be aware that deliveries will be arranged to a ground floor warehouse/goods in, unless previously discussed and agreed with Thermo Fisher Scientific Area Account manager who coordinated this instrument purchase" & Chr(10) & "" & Chr(10) & "If a non-standard delivery is required (e.g. to a 1st floor laboratory) and is not included in the shipping of this instrument, please contact us and we can arrange a quotation from a specialist courier for you" & Chr(10) & "" & Chr(10) & "If you have any questions in the meantime please do not hesitate to contact us." & Chr(10) & "" & Chr(10) & "Kind Regards"




 'Body of the email


SendEmail varName, varSubject, varBody1 & varBody2, True


'Send email command. The True after "varBody" allows user to edit email before sending.
 'The False at the end will not send it as a Template File

End Function
I am guessing your coding was the "SendEmail" bit at the end as the rest looks like what i typed, checked this and it did not work.

StuartG is offline   Reply With Quote
Old 01-11-2019, 08:25 AM   #34
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,131
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Hi Stuart. You'll have to give us more to go on. What does "did not work" mean? Did you get an error? If so, what was the error message? You are correct, all I did was replaced the SendObject line with the call to Daniel's function (did I not spell it correctly?). I thought you said your code worked and you also said Daniel's function worked. So, I am not sure why simply combining the two wouldn't work. Perhaps you can post some screenshots or a sample copy of your db, so we can get to the bottom of this issue quicker.
__________________
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.

Last edited by theDBguy; 01-11-2019 at 09:12 AM.
theDBguy is online now   Reply With Quote
Old 01-14-2019, 01:08 AM   #35
StuartG
Newly Registered User
 
Join Date: Sep 2018
Location: United Kingdom
Posts: 73
Thanks: 7
Thanked 0 Times in 0 Posts
StuartG is on a distinguished road
Re: Access creating Emails from stored data..

Quote:
Originally Posted by theDBguy View Post
Hi Stuart. You'll have to give us more to go on. What does "did not work" mean? Did you get an error? If so, what was the error message? You are correct, all I did was replaced the SendObject line with the call to Daniel's function (did I not spell it correctly?). I thought you said your code worked and you also said Daniel's function worked. So, I am not sure why simply combining the two wouldn't work. Perhaps you can post some screenshots or a sample copy of your db, so we can get to the bottom of this issue quicker.
Hello DBguy,
Sorry I guess it helps, espicially to help diagnose the problem.
I received no error message it was more that it did not do what i had intended.

The code you asked me to write in the immediate using Daniels code worked, however it did not ask me to attach files which is what I wanted.

I attach a sample of my DB, which has a few examples in to use. I will explain the following:

There is a module called " Acknowledgement" this was a code written and is exactly the output that I am after, with the exception that it does not automatically attach a file (This is needed), this is the one that I have been having issues with.

There is a "Macro" called Email Test, this used the inbuilt functions of Access, however this does not allow me to attach multiple attachments, only the attachment of the report i created behind it. (Report called, Acknowledgement of Receipt)
I used this function also because it allowed me to map the existing fields in my DB without me creating some convoluted code.

I am unsure what is the best option here.
Ideally I would prefer to use the Acknowledgment code because it enables the user to edit the content of the email before sending, whereas the second option is dictated by the report i created.

Hope this helps?
Stuart
Attached Files
File Type: zip Sales Database v5 TEST with Tables.zip (1.15 MB, 16 views)
StuartG is offline   Reply With Quote
Old 01-14-2019, 06:40 AM   #36
StuartG
Newly Registered User
 
Join Date: Sep 2018
Location: United Kingdom
Posts: 73
Thanks: 7
Thanked 0 Times in 0 Posts
StuartG is on a distinguished road
Re: Access creating Emails from stored data..

Quote:
Originally Posted by StuartG View Post
Hello DBguy,
Sorry I guess it helps, espicially to help diagnose the problem.
I received no error message it was more that it did not do what i had intended.

The code you asked me to write in the immediate using Daniels code worked, however it did not ask me to attach files which is what I wanted.

I attach a sample of my DB, which has a few examples in to use. I will explain the following:

There is a module called " Acknowledgement" this was a code written and is exactly the output that I am after, with the exception that it does not automatically attach a file (This is needed), this is the one that I have been having issues with.

There is a "Macro" called Email Test, this used the inbuilt functions of Access, however this does not allow me to attach multiple attachments, only the attachment of the report i created behind it. (Report called, Acknowledgement of Receipt)
I used this function also because it allowed me to map the existing fields in my DB without me creating some convoluted code.

I am unsure what is the best option here.
Ideally I would prefer to use the Acknowledgment code because it enables the user to edit the content of the email before sending, whereas the second option is dictated by the report i created.

Hope this helps?
Stuart
Hi There,
OK, i have been doing some more research this morning and afternoon as the day to day at work has been rather slow.

Is there a way that I can link my database to attach a particular file (PDF) each time the user goes to send either and acknowledgment or confirmation?
StuartG is offline   Reply With Quote
Old 01-15-2019, 08:54 AM   #37
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,131
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Hi Stuart. We can make the code do whatever we want, we just needed to know your ultimate goal. For example, how will the user initiate the action to send an acknowledgement or confirmation? Also, will the "particular PDF file" be the same every time?

__________________
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
Old 01-15-2019, 09:37 AM   #38
StuartG
Newly Registered User
 
Join Date: Sep 2018
Location: United Kingdom
Posts: 73
Thanks: 7
Thanked 0 Times in 0 Posts
StuartG is on a distinguished road
Re: Access creating Emails from stored data..

Quote:
Originally Posted by theDBguy View Post
Hi Stuart. We can make the code do whatever we want, we just needed to know your ultimate goal. For example, how will the user initiate the action to send an acknowledgement or confirmation? Also, will the "particular PDF file" be the same every time?


Hi dbguy.
Ok, thanks.

So my ultimate goal is..
the user clicks a button this initiates “acknowledgment of receipt” it uses the report I created in access but then in addition to the form output it also attaches our terms and conditions.

Note the terms and conditions PDF never changes..

The form I created is dictated by the DB ID this then pulls al information for that record and populates the report.

Please note that in addition to this there will be multiple buttons doing the same thing, though the wording will be different in “order confirmation pdf” but I would still use the inbuilt email feature but each output must contain the Ts and Cs each time..

Hope that makes sense and i haven’t complicated it..

Stuart


Sent from my iPhone using Tapatalk
StuartG is offline   Reply With Quote
Old 01-15-2019, 10:05 AM   #39
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,131
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Okay, wherever this button is, write code behind it to construct the body of the email. To verify it, just use a MsgBox command. For example:


Code:
Dim strBody As String


strBody = "Dear " & Me.CustomerName & "," & vbCrLf & "Please see attached files."


MsgBox strBody
Once you have that, you can use Daniel's function to add attachments like so, assuming the file is located at c:\myfolder\myfile.pdf


Code:
SendEmail strTo, strSubject, strBody, True, , "c:\myfolder\myfile.pdf"

__________________
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
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access used to sent emails from Lotus Notes emails not saved in Sent folder. Ajit Singh Modules & VBA 1 02-07-2016 10:55 PM
Auto Create Emails in Outlook based on Data in Access ConfusedBex Modules & VBA 9 04-30-2015 06:15 PM
Creating a VBA in Access 2013 To Send Emails bookworm4170 Modules & VBA 3 05-13-2013 01:27 PM
Question Emails - extracting data to Access matthewnsarah07 General 1 06-29-2008 11:01 AM
Emails in Stored Procedures With Paramaters and Spam Query KevinM SQL Server 1 02-01-2007 02:28 PM




All times are GMT -8. The time now is 11:16 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