Open Emails

  • Thread starter Thread starter Nich
  • Start date Start date
N

Nich

We have an email address that gets request emails which
have a word template document attached to them. I want to
have Access open the new emails, open the word attachment,
read in the bookmark fields to a table, change the subject
of the email to append the reference# from Access to it
and save the email then close it. I can have Access read
in a word doc to a table via code, but I'm not yet sure of
the correct function calls (or if even possible) for
Outlook. Anybody know any good functions or good
resources for this? I want the code to go to a specific
email box, open an email, read its subject line, open it's
attachment, edit its subject line and save it. So i need
function calls that perform these operations.

Thanks,
Nich
 
You have to set a reference to Microsoft Outlook

Public Function SendMail(strTo as String, strSubject as
String, strBody as String, Optional strFile)
Dim out As Outlook.Application
Dim itm As Outlook.MailItem

Set out = New Outlook.Application
Set itm = out.CreateItem(olMailItem)
itm.To = strTo
itm.Subject = strSubject
itm.Body = strBody
If Not IsMissing(strFile) then
itm.Attachments.Add strFile
End If

itm.Send
Set itm = Nothing
Set out = Nothing

End Function

Chris
 
In a code module, use Tools References to set one for Outlook.
Then modify this code to do what you need.

Public Sub SaveAttachment(strPath As String)
On Error GoTo Err_SaveAttachment

Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim MyInbox As Outlook.Items
Dim fldr As Outlook.MAPIFolder
Dim itm As Outlook.MailItem
Dim mFile As String, NumAttachments As Integer, i As Integer, NumEmails As
Integer, strTo As String

Set ns = ol.GetNamespace("MAPI")
Set MyInbox = ns.GetDefaultFolder(olFolderInbox).Items

'set a reference to a folder to move the items to
Set fldr = ns.Folders("Personal Folders").Folders("Saved
Messages").Folders("Bids")

For Each itm In MyInbox
'Debug.Print itm.Subject, itm.To, itm.SenderName
If itm.Subject Like "*Bid*" Then
NumAttachments = itm.Attachments.Count
i = 1 'attachment number
Do While i <= NumAttachments
mFile = itm.Attachments.Item(i).filename
itm.Attachments.Item(i).SaveAsFile strPath & mFile
i = i + 1
Loop
Else
'Debug.Print "Not a Bid"
End If
Next

'In order to move all messages from one folder to another, you must loop
backwards through the index
NumEmails = MyInbox.Count
For i = NumEmails To 1 Step -1
If MyInbox.Item(i).Subject Like "*Bid*" Then
Set itm = MyInbox.Item(i)
'Get e-mail address for the acknowledgment from the Body of the
original message
strTo = GetAddress(itm.Body)
Call SendEmailMessage("This is to acknowledge that your Bid has been
received and will be processed shortly.", "This is the body of your message
to us:" & vbCRLF & itm.Body, strTo)
itm.Move fldr
End If
Next i

Exit_SaveAttachment:
Set itm = Nothing
Set MyInbox = Nothing
Set ns = Nothing
Set ol = Nothing
Exit Sub

Err_SaveAttachment:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
Resume Exit_SaveAttachment

End Sub

Sub SendEmailMessage(strSubject As String, strBody As String, strTo As
String)
On Error GoTo Err_SendEmailMessage
Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim newMail As Outlook.MailItem

Set ns = ol.GetNamespace("MAPI")
Set newMail = ol.CreateItem(olMailItem)
With newMail
.Subject = strSubject
.Body = strBody & vbCRLF
With .Recipients.Add(strTo)
.Type = olTo
End With
.Send
End With

Exit_SendEmailMessage:
Set ol = Nothing
Set ns = Nothing
Set newMail = Nothing
Exit Sub

Err_SendEmailMessage:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
Resume Exit_SendEmailMessage

End Sub

You may need to use a program named ClickYes! in order to use the
SendEmailMessage code.
Oultook security now pops up dialog boxes that you can't program around.
ClickYes! looks for them and "clicks the Yes button" for your code.

http://www.express-soft.com/mailmate/clickyes.html
 
Back
Top