Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
Hi there,
I am trying to write a macro that will search my outlook inbox for a specific subject heading then save the attatchment to a specific folder. I have picked up this code with help from this site but my VBA skills are extremely limited and I can't get it to work. When I run it It stops at the first line and gives this error message: Compile error: User-defined type not defined Code: Sub SaveAttachments() Set myOLApp = CreateObject("Outlook.Application") Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\" For i = Fldr.Items.Count To 1 Step -1 Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls" End If Next olAtt olMi.Save olMi.Move MoveToFldr End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Can anyone tell me what I'm doing wrong? Any help would be greatfully appreciated. Thanks in advance Jamie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
Hi Jamie
You must set a reference (VBE - Tools - References) to the Outlook Object Library. -- Regards Ron de Bruin http://www.rondebruin.nl "Jamie" wrote in message ... Hi there, I am trying to write a macro that will search my outlook inbox for a specific subject heading then save the attatchment to a specific folder. I have picked up this code with help from this site but my VBA skills are extremely limited and I can't get it to work. When I run it It stops at the first line and gives this error message: Compile error: User-defined type not defined Code: Sub SaveAttachments() Set myOLApp = CreateObject("Outlook.Application") Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\" For i = Fldr.Items.Count To 1 Step -1 Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls" End If Next olAtt olMi.Save olMi.Move MoveToFldr End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Can anyone tell me what I'm doing wrong? Any help would be greatfully appreciated. Thanks in advance Jamie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
Set a reference to the Microsoft Outlook type library in the VBE
(ToolsReferences) -- HTH Bob Phillips "Jamie" wrote in message ... Hi there, I am trying to write a macro that will search my outlook inbox for a specific subject heading then save the attatchment to a specific folder. I have picked up this code with help from this site but my VBA skills are extremely limited and I can't get it to work. When I run it It stops at the first line and gives this error message: Compile error: User-defined type not defined Code: Sub SaveAttachments() Set myOLApp = CreateObject("Outlook.Application") Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\" For i = Fldr.Items.Count To 1 Step -1 Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls" End If Next olAtt olMi.Save olMi.Move MoveToFldr End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Can anyone tell me what I'm doing wrong? Any help would be greatfully appreciated. Thanks in advance Jamie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
Oh, and remove the line
Set myOLApp = CreateObject("Outlook.Application") -- HTH Bob Phillips "Jamie" wrote in message ... Hi there, I am trying to write a macro that will search my outlook inbox for a specific subject heading then save the attatchment to a specific folder. I have picked up this code with help from this site but my VBA skills are extremely limited and I can't get it to work. When I run it It stops at the first line and gives this error message: Compile error: User-defined type not defined Code: Sub SaveAttachments() Set myOLApp = CreateObject("Outlook.Application") Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\" For i = Fldr.Items.Count To 1 Step -1 Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls" End If Next olAtt olMi.Save olMi.Move MoveToFldr End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Can anyone tell me what I'm doing wrong? Any help would be greatfully appreciated. Thanks in advance Jamie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
Thanks a lot for that. I have one further question though.
At the moment this code saves the attachment as the name of the person who sent it which is great except when someone sends two. If this happens the first spreadsheet from that person is overwritten. Is there any way around this? "Ron de Bruin" wrote: Hi Jamie You must set a reference (VBE - Tools - References) to the Outlook Object Library. -- Regards Ron de Bruin http://www.rondebruin.nl "Jamie" wrote in message ... Hi there, I am trying to write a macro that will search my outlook inbox for a specific subject heading then save the attatchment to a specific folder. I have picked up this code with help from this site but my VBA skills are extremely limited and I can't get it to work. When I run it It stops at the first line and gives this error message: Compile error: User-defined type not defined Code: Sub SaveAttachments() Set myOLApp = CreateObject("Outlook.Application") Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\" For i = Fldr.Items.Count To 1 Step -1 Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & olMi.SenderName & ".xls" End If Next olAtt olMi.Save olMi.Move MoveToFldr End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Can anyone tell me what I'm doing wrong? Any help would be greatfully appreciated. Thanks in advance Jamie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving attachments from Outlook
You can add the date and/or time after the person's name
olAtt.SaveAsFile MyPath & olMi.SenderName & now() & ".xls |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot open Excel (.xls) attachments in Outlook | Excel Discussion (Misc queries) | |||
Excel Attachments not opening in Outlook | Excel Discussion (Misc queries) | |||
Outlook Webmail and Attachments | Excel Discussion (Misc queries) | |||
unblock .exe attachments in outlook | Excel Programming | |||
Open Outlook from Excel and send attachments | Excel Programming |