Open .MSG File Using VBA

[smartguy]

Hello all,

I have excel file in the below format.

i saved outlook maill in my c Drive. (.msg) based on condition i want to open the mail.

Excel file :

Excel Workbook
	A	B
1	Mail ID	Status
2	25	Done
3	26	Done
Sheet1

Path : C:\mail1

if Mail Id = "25" and Status = "Done" then

Open "C:\mail1\25.msg"

Please help to provide the solution....

 

[smartguy]

Please help....

 

[Ruddles]

I'm not 100% sure what you're asking for but if you want to loop through the worksheet opening all the filenames, you could do something like this:-

Option Explicit
 
Public Sub OpenMsgFiles()
 
  Dim iLast As Long
  Dim iRow As Long
  Dim sFile As String
  
  With ThisWorkbook.Sheets("Sheet1")
    iLast = .Cells(.Rows.Count, 1).End(xlUp).Row
    For iRow = 2 To iLast
      If .Cells(iRow, 2) = "Done" Then
        sFile = "C:\Mail1\" & .Cells(iRow, 1).Value & ".msg"
     [COLOR=green]   ' Open sFile (somehow)
[/COLOR]      End If
    Next iRow
  End With
 
End Sub

You'd have to provide the code to open the actual file as I don'y know off the top of my head how to do that.

Is that what you're trying to do?

 

[smartguy]

Yes Correct...

I want to open the file....

but its not opening

 

[Ruddles]

How are you trying to open it? What code are you using?

Or is it that you don't know how to open it at all?

Have you tried:-

Shell "notepad " & sFile

Or do you want to open it in your default mail client?

 

[smartguy]

Yes i want to Open default mail client....

 

[smartguy]

Macro Working fine. but it was open in Notepad.....

I want to Open the file in mail client......

 

[Ruddles]

Place this at the top of your code module. It must be a standard code module, not a worksheet code module:-

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As _
String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

And use this to open the file:-

ShellExecute 0, "Open", sFile, "", "C:\Mail1", SW_SHOWNORMAL

 

[smartguy]

Great it's Working fine.......

 

[Ruddles]

Great.

http://www.mrexcel.com/forum/showthread.php?t=551148&nojs=1#goto_threadrating