Open saved Message msg and replace subject

[oplintx]

I have several directories with saved OUTLOOK .msg templates. I have a spreadsheet with column A with saved paths to each file, and in column B, a subject line that I would like to replace the subject line with.

I would like to be able to click a button in "sheet 1" and it would open the msg file path in A2, replace the subject line with the contents in B2, then save the file, then loop through A:B in the spreadsheet until it ends. I know there may need to be some delays as it loops through to keep it from erroring.

I am using this to email daily pricing information to my customers and have to manually change the date to tomorrows date.

Please advise if this is possible in Excel VBA.

THANKS

 

[oplintx]

HERE IS AN EXAMPLE OF WHY MY DATA LOOKS LIKE

A1 A2
File Path Replace Subject with
C:\PRICING\WEST\YOUNG.MSG YOUNG PRICING 2/23
C:\PRICING\WEST\LITTLE.MSG LITTLE PRICING 2/23
C:\PRICING\WEST\PLAINS.MSG PLAINS PRICING 2/23
C:\PRICING\EAST\REED.MSG REED PRICING 2/23
C:\PRICING\EAST\TAYLOR.MSG TAYLOR PRICING 2/23
C:\PRICING\EAST\HORN.MSG HORN PRICING 2/23
C:\PRICING\EAST\JONES.MSG JONES PRICING 2/23
C:\PRICING\NORTH\DAVIS.MSG DAVIS PRICING 2/23
C:\PRICING\NORTH\WHITE.MSG WHITE PRICING 2/23
C:\PRICING\NORTH\STATE.MSG STATE PRICING 2/23
C:\PRICING\SOUTH\LOGAN.MSG LOGANPRICING 2/23
C:\PRICING\SOUTH\BROTHER.MSG BROTHER PRICING 2/23
C:\PRICING\SOUTH\ATLANTA.MSG ATLANTA PRICING 2/23
C:\PRICING\SOUTH\BRENT.MSG BRENT PRICING 2/23

 

[FormR]

Hi - you could give this a try.

Sub m()


Dim v, i As Long
Dim oApp As Object, oMSG As Object
Set oApp = CreateObject("Outlook.application")


v = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value


For i = 1 To UBound(v)
    Set oMSG = oApp.CreateItemFromTemplate(v(i, 1))
    With oMSG
        .Subject = v(i, 2)
        .SaveAs (v(i, 1))
        .Close olDiscard
    End With
Next i


Set oMSG = Nothing
Set oApp = Nothing


End Sub

 

[oplintx]

Wow, that worked like a champ!!! Thank you so much!!!

 

[FormR]

Cool! Although on reflection I would change this line:

  .Close olDiscard

to:

  .Delete

Else I fear you may end up filling your Drafts folder.