How to send all Outlook mail items stored as *.msg in folder

Question

I have a program that creates multiple email messages and save them in a folder on a shared drive mapped as "Y:" as .msg files. Each message has a different recipient, subject and message body.

In order to send these messages using my outlook I wrote this code, but it is generating an error "Object Required 424" and debugger highlights line "Set MySource = . . . .". Any advise please

Sub SendMSGs()

    Dim MyItem As Outlook.MailItem, MySource As Object, file As Variant
    Set MySource = MyObject.GetFolder("Y:\UI_messages\")
    For Each file In MySource.Files
        Set MyItem = file.Name.msg
        MyItem.Send
    Next file

End Sub

`MyObject` should be a FileSystemObject. `Name` is a property of `file` but `msg` is not a property of `file.Name`. Did you mean `file.Name & ".msg"`? If so, property `Name` already includes the extension. However, `file.Name` does not include the path so you would need to add that. `MyItem` is of type `MailItem`. `file.Name` is of type `String`. You cannot set a `MailItem` to a `String`. Have a look at this answer, https://stackoverflow.com/a/19426859/973283, for instructions on converting a filename to a mail item. — Tony Dallimore, Dec 05 '18 at 19:48

score 0 · Answer 1 · answered Dec 05 '18 at 20:41

0

I have not tested this code or even attempted to compile it. It fixes the most obvious errors in your code so at least you will get a better class of error.

Technique for converting filename to mail item from: https://stackoverflow.com/a/19426859/973283.

This code needs a reference to "Microsoft Scripting Runtime". Without this, File, Folder and FileSystemObject with be unknown types.

Sub SendMSGs()

  Dim FileCrnt As File
  dim FldrCrnt As Folder
  Dim Fso As FileSystemObject
  Dim ItemCrnt As MailItem
  Dim Path As String

  Path = "Y:\UI_messages\"

  Set Fso = CreateObject("Scripting.FileSystemObject")
  Set FldrCrnt = Fso.GetFolder(Path)

  For Each FileCrnt In FldrCrnt.Files
    If LCase(Right$(FileCrnt.Name, 4)) = ".msg" Then 
      Set ItemCrnt = Application.CreateItemFromTemplate(Path & FileCrnt.Name)
      ItemCrnt.Send
    End If
  Next FileCrnt

End Sub

answered Dec 05 '18 at 20:41

Tony Dallimore

12,335
7
32
61

I tried this code, but it is generating this error: User-Defined Object not defined on "Dim FileCrnt...." line. – CodeHunter Dec 05 '18 at 21:32
Sorry error is : "user-defined type not defined." Just so you know that I already have "Microsoft Outlook 16.0 Object Library" checked in the reference. – CodeHunter Dec 05 '18 at 21:41
As I said at the top of the code, this code needs "Microsoft Scripting Runtime". Within the Editor, click Tools then References. Scroll down the list (which is in alphabetic sequence) until you reach "Microsoft Scripting Runtime". Click the box on the left to tick it then click OK. If you click Tools then References again, "Microsoft Scripting Runtime" will be near the top and ticked. "Microsoft Outlook 16.0 Object Library" gives you access to objects such as `MailItem` it does not give you access to objects such as `FileSystemObject` which you need to use `GetFolder`. – Tony Dallimore Dec 05 '18 at 23:36
Without its libraries, VBA is a very limited language. Some libraries are automatically ticked such as "Microsoft Outlook 16.0 Object Library" for Outlook and "Microsoft Excel 16.0 Object Library" for Excel. Anything that is not standard for a particular Office product will have to be explicitly referenced. If you go hunting for code, check what references it needs. A few authors forget to mention required libraries. If you get a message saying "xxxxxx" is an undefined user type, look up "xxxxxx" and you should be able to find a website which tells you which library defines it. – Tony Dallimore Dec 05 '18 at 23:43
I checked "Microsoft Scripting Runtime" in the reference and now I am getting "Run time error 13: Type mismatch" on "Set FldrCrnt...." line. Any suggestions? – CodeHunter Dec 05 '18 at 23:48
That bit of code was copied almost unchanged from one of my Excel macros so I could not see the cause of the problem. After some thought I realised the problem is that a Windows `Folder` as returned by `GetFolder` is not an Outlook `Folder`, Try `FldrCrnt As Scripting.Folder`. It is gone midnight here so I will not reply again until the morning. If you have anymore problems, I will create some msg files so I can test my code. – Tony Dallimore Dec 06 '18 at 00:10
Change **`Dim FldrCrnt As Folder`** To **`Dim FldrCrnt As Object`** – 0m3r Dec 06 '18 at 07:40
1

@0m3r I agree that `Dim FldrCrnt As Object` would have avoided this problem. However, in general, properly typing variables reduces the incidence of obscure errors. One case where not typing an object is helpful does not make this good advice. – Tony Dallimore Dec 06 '18 at 13:23

score 0 · Answer 2 · answered Dec 06 '18 at 07:35

Should be - Example

Option Explicit
Sub SendMSGs()
    Dim MyItem As Outlook.MailItem

    Dim MyObject As Object
    Set MyObject = CreateObject("Scripting.FileSystemObject")

    Dim MySource As Object
    Set MySource = MyObject.GetFolder("C:\Temp\")

    Dim oFile As Object
    For Each oFile In MySource.Files
        If LCase(Right$(oFile.Name, 4)) = ".msg" Then
            Set MyItem = Application.CreateItemFromTemplate("C:\Temp\" & oFile.Name)
            MyItem.Display
        End If
    Next oFile

End Sub

Try using Option Explicit

@Tony Dallimore Answer Should work if you change Dim FldrCrnt As Folder To Dim FldrCrnt As Object

How to send all Outlook mail items stored as *.msg in folder

2 Answers2