Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-09-2019, 11:49 AM   #16
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 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
Does anyone think that this is the best way forward or do I need to rethink my idea?
Hi. You might find this approach better.

__________________
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 offline   Reply With Quote
Old 01-09-2019, 11:57 AM   #17
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. You might find this approach better.


Would I just copy this code and then adjust to my needs? VBA is all very new to me so sorry if I知 asking lots of questions..


Sent from my iPhone using Tapatalk
StuartG is offline   Reply With Quote
Old 01-09-2019, 11:58 AM   #18
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

You would copy the code and put it in a Standard Module. Then, you just use it by calling the function and passing all the necessary arguments to send an email.

__________________
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 offline   Reply With Quote
Old 01-09-2019, 12:04 PM   #19
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
You would copy the code and put it in a Standard Module. Then, you just use it by calling the function and passing all the necessary arguments to send an email.


Ok thank you. What I値l do is put it all together tomorrow and do some trial and errors, if i get stuck I値l ask the forum again..


Sent from my iPhone using Tapatalk
StuartG is offline   Reply With Quote
Old 01-09-2019, 12:05 PM   #20
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Good luck!
__________________
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 offline   Reply With Quote
Old 01-10-2019, 07:26 AM   #21
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
Good luck!
Oh this was fun and been a head scratching thing, with still no results. I copied the code per your advisement and i still cannot get it open an outlook message window.
What have i done wrong?

Function SendEmail()

Dim strTo As String
Dim strSubject As String
Dim strBody As String
Dim bEdit As Boolean

'Send Email using late binding to avoid reference issues
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim i As Integer
Const olMailItem = 0

On Error GoTo ErrorMsgs

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("stuart.ginns@thermofisher.com")
objOutlookRecip.Type = 1

If Not IsMissing(strBCC) Then
Set objOutlookRecip = .Recipients.Add
objOutlookRecip.Type = 3
End If

.Subject = strSubject
.Body = strBody
.Importance = 2 'Importance Level 0=Low,1=Normal,2=High

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
If IsArray(AttachmentPath) Then
For i = LBound(AttachmentPath) To UBound(AttachmentPath) - 1
If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
End If
Next i
Else
If AttachmentPath <> "" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
End If
End If

For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next

If bEdit Then 'Choose btw transparent/silent send and preview send
.Display
Else
.Send
End If
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message. For more information, " & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp."
Exit Function
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If
End Function
StuartG is offline   Reply With Quote
Old 01-10-2019, 07:51 AM   #22
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,104
Thanks: 105
Thanked 2,447 Times in 2,249 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Access creating Emails from stored data..

Another approach is to use Collaborative Data Objects (CDO).
This allows you to send emails either via Outlook or direct from Access.
You can send plain text or HTML emails, include both images and data and also include attachments.
In other words, almost everything you can do by sending an email from Outlook itself.
All you need to know are your email account settings.
If you are interested, try my CDO Email Tester

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 01-10-2019, 08:47 AM   #23
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 isladogs View Post
Another approach is to use Collaborative Data Objects (CDO).
This allows you to send emails either via Outlook or direct from Access.
You can send plain text or HTML emails, include both images and data and also include attachments.
In other words, almost everything you can do by sending an email from Outlook itself.
All you need to know are your email account settings.
If you are interested, try my CDO Email Tester
Thank you for the tip, i will have a look at this also, is this just a simple export the items from this Database and then import to my current DB?

I am intrigued though on where all the message text goes or is it all derived from an attachment?

The code I had used previously creates a memo style email which the user can edit before sending, however I need to attach the same attachment after i open everything.
StuartG is offline   Reply With Quote
Old 01-10-2019, 11:28 AM   #24
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 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 this was fun and been a head scratching thing, with still no results. I copied the code per your advisement and i still cannot get it open an outlook message window.
What have i done wrong?
Hi. It doesn't look like you copied the code correctly. You weren't supposed to make any changes to it. For example, you said you have the following at the top of your code:


Code:
 Function SendEmail()
Whereas, the one from the website looks like this:
Code:
Function SendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _                    Optional strBCC As Variant, Optional AttachmentPath As Variant)
See all those missing stuff? If you still want to try Daniel's function, try the following steps:


1. Copy the code from his website
2. Go to the VBE window and create a new Standard Module
3. Paste the code in the VBE window
4. Save the module and call it something like modSendEmail
5. In the Immediate Window, try entering the following
Code:
SendEmail "testemail@somewhere.com","Subject","Message",True
Let us know what happens...
__________________
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 offline   Reply With Quote
Old 01-10-2019, 11:52 AM   #25
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. It doesn't look like you copied the code correctly. You weren't supposed to make any changes to it. For example, you said you have the following at the top of your code:


Code:
 Function SendEmail()
Whereas, the one from the website looks like this:
Code:
Function SendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _                    Optional strBCC As Variant, Optional AttachmentPath As Variant)
See all those missing stuff? If you still want to try Daniel's function, try the following steps:


1. Copy the code from his website
2. Go to the VBE window and create a new Standard Module
3. Paste the code in the VBE window
4. Save the module and call it something like modSendEmail
5. In the Immediate Window, try entering the following
Code:
SendEmail "testemail@somewhere.com","Subject","Message",True
Let us know what happens...
OK, so this is copied exactly from his website.
When i click the play button in the VBA to test code it asks me to save a macro?

Option Compare Database

---------------------------------------------------------------------------------------
' Procedure : SendEmail
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Automate Outlook to send emails with or without attachments
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTo To Recipient email address string (semi-colon separated list)
' strSubject Text string to be used as the email subject line
' strBody Text string to be used as the email body (actual message)
' bEdit True/False whether or not you wish to preview the email before sending
' strBCC BCC Recipient email address string (semi-colon separated list)
' AttachmentPath single value or array of attachment (complete file paths with
' filename and extensions)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' ************************************************** ************************************
' 1 2007-Nov-16 Initial Release
'---------------------------------------------------------------------------------------
Function SendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _
Optional strBCC As Variant, Optional AttachmentPath As Variant)
'Send Email using late binding to avoid reference issues
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim i As Integer
Const olMailItem = 0

On Error GoTo ErrorMsgs

Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = 1

If Not IsMissing(strBCC) Then
Set objOutlookRecip = .Recipients.Add(strBCC)
objOutlookRecip.Type = 3
End If

.Subject = strSubject
.Body = strBody
.Importance = 2 'Importance Level 0=Low,1=Normal,2=High

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
If IsArray(AttachmentPath) Then
For i = LBound(AttachmentPath) To UBound(AttachmentPath) - 1
If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
End If
Next i
Else
If AttachmentPath <> "" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
End If
End If

For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next

If bEdit Then 'Choose btw transparent/silent send and preview send
.Display
Else
.Send
End If
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message. For more information, " & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp."
Exit Function
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If
End Function
StuartG is offline   Reply With Quote
Old 01-10-2019, 12:00 PM   #26
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Hi. No, don't click on the play button. Instead, go to the Immediate Window (Ctrl+G) and type the code I posted earlier to test it.
__________________
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 offline   Reply With Quote
Old 01-10-2019, 12:04 PM   #27
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. No, don't click on the play button. Instead, go to the Immediate Window (Ctrl+G) and type the code I posted earlier to test it.
got ya, ok, so that worked, it bought up the outlook new message window in the format you had specified in the code.
StuartG is offline   Reply With Quote
Old 01-10-2019, 12:08 PM   #28
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

Excellent! So now, in the code where you're trying to send an email, all you have to do is construct the pieces you need to pass as arguments. For example, you might have something like:


Code:
Dim ToAddress As String
Dim EmailSubj As String
Dim EmailMessage As String

ToAddress = "someone@somewhere.com"
EmailSubj = "Test Email"
EmailMessage = "This is the message body of the email."

SendEmail ToAddress, EmailSubj, EmailMessage, True
__________________
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 offline   Reply With Quote
Old 01-10-2019, 12:12 PM   #29
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
Excellent! So now, in the code where you're trying to send an email, all you have to do is construct the pieces you need to pass as arguments. For example, you might have something like:


Code:
Dim ToAddress As String
Dim EmailSubj As String
Dim EmailMessage As String

ToAddress = "someone@somewhere.com"
EmailSubj = "Test Email"
EmailMessage = "This is the message body of the email."

SendEmail ToAddress, EmailSubj, EmailMessage, True
is this somewhere in the code that you recommended or do i add this at the bottom of existing code i pasted?
StuartG is offline   Reply With Quote
Old 01-10-2019, 12:15 PM   #30
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 552 Times in 540 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Access creating Emails from stored data..

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.

__________________
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 offline   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 08:14 PM.


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