help with vbcode to move an attachment in outlook to a new folder on the drive

  • Thread starter Thread starter Steve Childs
  • Start date Start date
S

Steve Childs

Help! Please. This one's driving me insane. I need some
VB code to lookin in the inbox in Outlook and copy all
the .xls attachments to a new directory (folder) on the
harddrive. This has puzzled me for weeks and I can't find
what I need from MS.
Many Thanks
Steve
 
Today is you lucky day then!

I wrote this a while back: (watch the line wrapping)

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
 
Hi Steve,

This newsgroup is for questions about using Microsoft Access with
external data sources, so it's not the best place to look for help on
programming the Outlook object model. If you can't find what you need at
www.slipstick.com, I suggest you try posting in
microsoft.public.outlook.program_vba .

Help! Please. This one's driving me insane. I need some
VB code to lookin in the inbox in Outlook and copy all
the .xls attachments to a new directory (folder) on the
harddrive. This has puzzled me for weeks and I can't find
what I need from MS.
Many Thanks
Steve

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
I fail to see your problem. I too have a similar need and this was one of
the NG options I considiered a valid place to look. I am programming in
Access trying to read data that lies external to Access just as Steve is
trying to work with data outside of access. Why is this a problem? This
seems a pretty anal response especially considering I was able to find a
solution here in a reply to the original post.


John Nurick said:
Hi Steve,

This newsgroup is for questions about using Microsoft Access with
external data sources, so it's not the best place to look for help on
programming the Outlook object model. If you can't find what you need at
www.slipstick.com, I suggest you try posting in
microsoft.public.outlook.program_vba .

Help! Please. This one's driving me insane. I need some
VB code to lookin in the inbox in Outlook and copy all
the .xls attachments to a new directory (folder) on the
harddrive. This has puzzled me for weeks and I can't find
what I need from MS.
Many Thanks
Steve

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Steve's message asked about using Visual Basic to put Outlook
attachments into a folder - with no mention of VBA, Access or any
database issue at all. As Joe said, it was just a matter of luck that
had a partial solution available (partial because it needs modifying to
place the attachments into a file system folder rather than an Outlook
folder).


I fail to see your problem. I too have a similar need and this was one of
the NG options I considiered a valid place to look. I am programming in
Access trying to read data that lies external to Access just as Steve is
trying to work with data outside of access. Why is this a problem? This
seems a pretty anal response especially considering I was able to find a
solution here in a reply to the original post.


John Nurick said:
Hi Steve,

This newsgroup is for questions about using Microsoft Access with
external data sources, so it's not the best place to look for help on
programming the Outlook object model. If you can't find what you need at
www.slipstick.com, I suggest you try posting in
microsoft.public.outlook.program_vba .

Help! Please. This one's driving me insane. I need some
VB code to lookin in the inbox in Outlook and copy all
the .xls attachments to a new directory (folder) on the
harddrive. This has puzzled me for weeks and I can't find
what I need from MS.
Many Thanks
Steve

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
John,
Sorry - but that is a *full* solution.
Files are detached and moved to the folder strPath.
--
Joe Fallon
Access MVP



John Nurick said:
Steve's message asked about using Visual Basic to put Outlook
attachments into a folder - with no mention of VBA, Access or any
database issue at all. As Joe said, it was just a matter of luck that
had a partial solution available (partial because it needs modifying to
place the attachments into a file system folder rather than an Outlook
folder).


I fail to see your problem. I too have a similar need and this was one of
the NG options I considiered a valid place to look. I am programming in
Access trying to read data that lies external to Access just as Steve is
trying to work with data outside of access. Why is this a problem? This
seems a pretty anal response especially considering I was able to find a
solution here in a reply to the original post.


John Nurick said:
Hi Steve,

This newsgroup is for questions about using Microsoft Access with
external data sources, so it's not the best place to look for help on
programming the Outlook object model. If you can't find what you need at
www.slipstick.com, I suggest you try posting in
microsoft.public.outlook.program_vba .

Help! Please. This one's driving me insane. I need some
VB code to lookin in the inbox in Outlook and copy all
the .xls attachments to a new directory (folder) on the
harddrive. This has puzzled me for weeks and I can't find
what I need from MS.
Many Thanks
Steve

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top