Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sending Email from my Access Database to external non-GAL users

Posted on 2012-08-31
17
Medium Priority
?
231 Views
Last Modified: 2013-07-29
Ok, I was able to get internal emails to work in my database when someone is assigned a new task which my boss absolutely loved. Now, he wants to be able to do the same with when a part of the data project is available for the client. So I have a Status combobox that has "Available"

We want to be able to have an email that generates with the information about the data load sent to our client, which I have their email addresses in a table already with our group for the team Cc'd.  I can use the GAL for sending assignments to our team; however, they are not in the GAL.  Does anyone have any suggestions to send the email?  I could use Outlook then pull the email addresses of the clients to send it to.
0
Comment
Question by:lauriecking0623
  • 8
  • 5
  • 4
17 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38355561
The same basic principles should apply.

However with absolutely no knowledge of your existing process, I can't propose anything targeted.

Can you post a sample of your current DB:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38355572
You can use function from your previous question with little modifications:
Option Compare Database
Option Explicit

Function SEND_EMAIL_CLIENT(sTO_EMAIL As String, sCC_EMAIL as String, sHDR As String, sBODY As String)
On Error GoTo Handle_Error
Dim olAPPL As Outlook.Application, olMAIL_ITEM As Outlook.MailItem
Dim olACCT As Outlook.Account

Set olAPPL = New Outlook.Application
Set olMAIL_ITEM = olAPPL.CreateItem(Outlook.olMailItem)
'olMAIL_ITEM.Display

olMAIL_ITEM.Recipients.Add sTO_EMAIL
olMAIL_ITEM.CC = sCC_EMAIL
olMAIL_ITEM.Subject = sHDR
olMAIL_ITEM.BodyFormat = olFormatHTML
olMAIL_ITEM.HTMLBODY = sBODY
olMAIL_ITEM.Send

Set olAPPL = Nothing

End Function

Open in new window

Some comments: you are using function and it should return value. You are using On Error, but have no point where error will be evaluated.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38355616
als315,

I see you have this covered, ...thanks

;-)

Jeff
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lauriecking0623
ID: 38355631
@als315,

I do not see how it would pull the email address from the client table. Status contains "Available" so that would initiate the email when it is selected. Do you know how to do that?
0
 
LVL 40

Expert Comment

by:als315
ID: 38356671
@lauriecking0623: if you like to have exact code for your DB, make a sample DB as was adviced by Jeff Coachman.
@Jeff: :-)
0
 

Author Comment

by:lauriecking0623
ID: 38364542
I have attached the empty database where I am trying to add the external mail feature to. Open up the database, you can see what my form looks like for the FRM_DataObjects under Status combobox, I would like an email to go out to our clients once the status is marked available.
Database3.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38365277
Does your sample include the modifications als315 posted?
0
 

Author Comment

by:lauriecking0623
ID: 38365295
I did not make that modification just yet since I was confused.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38365586
OK,

Since it seems that you and als315 had worked together previously on this, please wait to see if he replies with any further info.

If not, then I will try to assist...

Jeff
0
 
LVL 40

Expert Comment

by:als315
ID: 38366660
@lauriecking0623: I don't see any table with clients email addresses. If it is only one client, you can add email to the form (may be as extra column in some combo box). You deleted too many tables from sample. Will be good to at least one working Data object for testing.
0
 

Author Comment

by:lauriecking0623
ID: 38367988
@als315

I will repost it for your look at.
0
 

Author Comment

by:lauriecking0623
ID: 38377469
@als315

Have you looked at my database? I have made the changes to my system. Just trying to understand how I can pull the external email to send the updates.
0
 
LVL 40

Expert Comment

by:als315
ID: 38378764
I'm waiting an example with clients emails. You should take email anywhere before sending mail.
0
 

Author Comment

by:lauriecking0623
ID: 38378793
Ok, I will add the emails and go from there.
0
 

Author Comment

by:lauriecking0623
ID: 38395851
@als315,

I have been able to revisit this problem in my database.  I was thinking about it more.  In the code, could I hard code the email addresses for our client into the VBA since those email addresses will not change at all???
0
 
LVL 40

Expert Comment

by:als315
ID: 38397659
Yes, It is sTO_EMAIL in function. Assign it to string value with this fixed email address.
0
 

Author Comment

by:lauriecking0623
ID: 38399029
Great. Thanks. I will try it and get back to you.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question