Access/Outlook Email Automation


I've just discovered "Automation" and am trying to implement it in my Access d/b to send reports and external file attachments via e-mail. I'm not a VB programmer but I've searched on the web to find some code that mostly works OK (extract below) - I have 3 questions on this that I haven't been able to figure out:
1) Is it possible to set up ".Body" to the contents of an external file - eg: an Access report saved in RTF or HTML format, (or any other external file for that matter), rather than having to hard-code the text?
2) What changes need to be made to the code to add more than 1 attachment?
3) Is it possible to set up a link in the body of the email to open the attachments? eg: "click here to view your quotation".
Any help will be much appreciated.
--------------------------------------
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
---------------------------------------------


Sponsored Links:



Hi,

I'm trying to automate saving new emails from outlook inbox (with attachements) to Access and attachemnts to the local drive. I've figured out how to do this using a button in an access form to trigger this but is there a way for Outlook to trigger this process so it's automated?

Can outlook trigger code in access when I get any new mail?

Might be looking at this backwards.

Thanks




I am aware of the Outlook.Application functionality.
Can VBA in Access be used to interact with other clients, such as Thunderbird, for example?
What about email automation on a Mac? I know there is no Access for Mac, but what about Excel email automation in Mac with clients other than outlook?
Thank you.




Hey guys,
Here is the issue. I am receiving emails (work related) in the same format (where the information is in the body of the Outlook email). Basically the emails contain some financial data that the team needs to manually enter in Access database.
The great question is - is there a way to somehow parse the emails and get the data from the email directly into an Access table?
The emails are coming into a group emailbox in Outlook.
Any assistance is greatly appreciated.

Regards
Atol




History -
I wrote the app in Access 2K and it sent email through Outlook97
We upgraded to Office 2003

I have a custome app in Access that utilizes the ability to send email through Outllook. It worked with Access2K and Outlook97 - it works in Access2K3 but Outlook gives a warning for every email that access is sending.

Outllook Warning: Another program is trying to send an email on your behalf. Do you want to let it? This may be a virus.

And then you have to wait like 5 seconds for the OK button to activate so you can let it send the mail. Is there any way around this? PLEASE TELL ME THERE IS


Here is how I am sending the mail...

Public Function SendEMail()
DoCmd.SendObject acSendNoObject, , , rs("Driver"), , , "TRUCK " & _
"UPDATE", EmailText, False
End Function




I have email automation established in VBA code. When I click a button on the database, it retrieves a list of email address from a table and automatically send the report via MS Outlook. It works fine on my PC. However, when I access the database from another PC and click the submit button it doesn't want to use Outlook. Instead it opens the Internet Connection Wizard. The PC is already connected to the internet.

I need it to recognize Outlook instead of trying to open Outlook Express.

Any ideas??




This is a strange one.

I have many computers using my database accross a workgroup.

Some PCs are win98, some winME, some XP. When my database wants to send emails automatically using the sendobject command all is fine apart from when the Xp machine does it.


It always defaults to the internet connection wizard and tries to follow this procedure through.

The outlook and email work fine using outlook normally, it just seems to be happending through automation.

does anyone know how to override this....


You could make my weekend very happy if you can.


Cheers

PAUL!





Hello,

I can generate an email by clicking on a button on a MS Access Report. In the body of the resulting Outlook email you have formatted text. I have also included an array of values which are displayed in this email but I need to highlight certain items in the array in red based on whether or not another column for that record is checked. Here is an image of the email. Notice that I have circled "ZWO" and that it is red. I need for this to happen programmically. Hope you can help. Thanks Linda
Attached Thumbnails     Reply With Quote 10-09-2012, 11:58 AM #2 rpeare VIP Windows XP Access 2003 Join Date Jul 2011 Posts 1,917 How are you currently generating this email, having it generate the body then manually changing the color yourself before sending?

I'm assuming you're using the sendobject method to generate the email?

If you are using sendobject I don't think you can do color formatting, you would have to blend html formatting (which I've never done) but here's an example using ASP which is similar to the code you'd use in a vb script

http://www.codeproject.com/Articles/...matted-Text-Ed

I've never done partial text formatting on email, I usually print reports to pdf's and email those out instead.

most of the stuff I looked at for your question was built around building an HTML based email rather than the sendobject command.




Hello, i have a button on my form that opens out an outlook email. Ont his part of the code:

With oMail
.To = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
'.Body =

is where i enter what i want the message to display, however i cant get the formatting right. Im using access 97. ive attached a word doc which shows EXACTLY how id like the email to open up with. can anyone help me make it look like that? thanks! Attached Files email format.doc (22.0 KB, 7 views) Reply With Quote 11-23-2011, 12:33 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,129 This article has code relevant to Access97 up. It shows using HTML tags so should be possible. http://www.everythingaccess.com/tuto...curity-Warning

If you need to learn about HTML tags check this site http://www.w3schools.com/html/




Access 2007
Outlook 2007

I am totally new to VB and Access. I would like to create a command button that would open a new Outlook email with the Subject and body already filled in. I tried the wizard already but to no avail. Could someone please help me with this. Eventually I want to be able fill in the "To" line with addresses from a specific record in the database but for now I just want Access to open a new email in outlook. Obviously Im totally new to this so step by step would be helpful.

Thanks




HI, hoping someone has something relatively simple, or can direct me to the right place, I want to open Outlook email application on click and attach a document (I have the full file path and file extension)

To date I've only ever used Docmd.sendobject , can I use this to send a document. My db isnt using actual attachments, I am just storing filepaths in a table. I guess then, next question is do I need to save the original document to the database first as some sort of temporary attachment perhaps?

I'm not interested in actually sending the email, or closing the app after, just to open if not already, add to: & subject: and attach a doc.

- - - - - - -

I've tracked down some code from the forums but I get error "user type not defined " on first line ... Dim Myoutlook as outlook.application

not a very good start I know, I am however using access 07 in case this is anything to do with, any help much appreciated

>> NOW FIXED ! -- In VBA tools, references menu I didnt have Microsoft Outlook 12.0 Object Library set, just checked the box and now all working fine! Code insert...


	Code:
	Public Function Email_now()

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim objOutlook As Object
Dim Attach As String

'Check if outlook if open
Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If objOutlook Is Nothing Then
 ' Enter code to open outlook...
    'still working on this !!
    End If

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
      
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = "test" '**put in reference to form

'MyMail.CC = MailList("Copy To")
'This gives it a subject
MyMail.Subject = "test" '**put in reference for subject
'This gives it the body
MyMail.Body = "test"
'If you want to send an attachment
'uncomment the following line
Attach = "C:usershome_steventest.txt"  '** add file path to attach doc
MyMail.Attachments.Add Attach

'This sends it!
'MyMail.Send
'To Display instead of Send, Uncomment the next line
'And comment the "MyMail.Send" line above this.
MyMail.Display

'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
End function





I have the following code that searches outlook emails for a specific word ("deadline" in this case) and then opens all emails that that contain that word. It all works perfectly, but if many emails are found, too many windows get opened.

Any suggestions how to modify this code, so it will just show and highlight all emails found in outlook (as if you would use the instant search feature in the attached picture).

Thanks.


	Code:
	Private Sub Command84_Click()
On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim nms As Outlook.NameSpace
   Dim fldInbox As Outlook.MAPIFolder
   Dim highligh As Outlook.Search
   Dim msg As Outlook.MailItem
   Dim itm As Object

   Set nms = appOutlook.GetNamespace("MAPI")
   Set fldInbox = nms.GetDefaultFolder(olFolderInbox)

   For Each itm In fldInbox.Items
      If itm.Class = olMail Then
         Set msg = itm
         If InStr(msg.Body, "deadline") > 0 Then
         'msg.
            msg.Display
         End If
      End If
   Next itm
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub





I was wondering if anyone has been able to copy an access report into the body of an outlook email.

I am tryin to copy only the active record. What I have done in the past was as follows:

Private Sub cmdEmailAssignedForm_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Improvement No] = " & Me.[Improvement No]
DoCmd.OpenReport "ReportEmailNoticeofAssigned", acViewPreview, , strWhere
End If
End Sub

I would like to improve this by just pasting the text on the active record into the email body.

Thanks for your help....

Hector




Hello everybody,

i want to make the following function in access: When i press a button, then outlook starts. i works great!! But now i want an existing word document as the body of the outlook email message


Code would be greatly appreciated


Thx,

Jimmy




I am using the following code which successfully generates an outlook email message with a template. but when I add the myItem.Body I lose my template (which is basically a blank email with a sigature). Any clues?

rivate Sub CbSendEmail_Click()
If IsNull(Me.[Email Address]) Then
MsgBox "No Email Address For This Contact", , "No Valid Email Address"
Else
Dim TEMPLATE As String
TEMPLATE = DLookup("EmailTemplate", "Tblconfiguration")
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItemFromTemplate(TEMPLATE)
myItem.Subject = "Integrasoft Ltd"
myItem.To = [Email Address]
myItem.Display
'myItem.Body = Forms.frmdatabase.AccountNumber THIS IS THE PROBLEM LINE WHERE IF DE-REM'D MY TEMPLATE DOES NOT DISPLAY
End If
End Sub




In an Access 2007 application I'm trying to read the properties of Outlook email messages stored on a server in related project folders. I have utilized treeview and listview controls that allow the user to see all the files within various project folders, but when the file type is an Outlook message I'd like to be able to provide additional information like Sender, Recipient, Subject and Date. Any forum members experienced with this type of challenge? Any direction or advice is greatly appreciated.

Ken




Hi Forum, ms access 2010.
Email from access using Outlook Express works ok but one new pc (64 bit) is Windows 7 and I have just found out Outlook Express isn't supported by Windows 7.

Research indicates Windows 7 options are Outlook or Windows Live.

Appreciate some advice on issues and best road to go down where users have M Access 2010 on pc's using Windows XP and 64bit Windows 7.

Would I need to filter the users to use different email code depending on what pc they have ?

Thanks in advance




I can successfully create a table link from Access to Outlook for some folders in my Personal Outlook folder, but not for others. Access appears to create the link, but there is no data in the linked table, though in Outlook there is data.

Any ideas as to what I should check?




Hi All,

Apologies if this question has already been asked. Im a bit of a newb when it comes to Access. I was wondering if its possible to create a input form/email in Access/Outlook in which I can send to a number of people via email that populate a Access database/xls when they reply?

Extra Info:
-Users may have a different version of office that I have.
-I have office 97, 2000 and 2003.

Hope that all makes sense.

Ollie




Hi
I was cleaning up a form and ms access suggested to optimize I should declare all variables. I placed the line. Option Explicit in doing so it forced me to Dim many variables in subsequently after doing this it wouldn't open ms outlook email and place the text in the specific email.

Am I doing something wrong as soon as I change it back to Option compare database and exclude the Dim lines ever thing works fine.

I know this is just being picky and thoughts?

Thanks

FYI it isn't holding me up it is just increasing my education.