Thread: Save Sent Message To....

I'm using a standard little Excel macro to send an individual email to each person in a list:

With ActiveSheet
    Msg = "All," & Chr(10) & Chr(10) & "As part of normal support and maintenance, this week we updated your store to v" & .[X1] & Chr(10) & Chr(10) & "Any questions please contact me directly."
    Set Emails = .Range("T2", .Range("T" & .Rows.Count).End(xlUp)).SpecialCells(xlFormulas, 2)
    
    For Each Email In Emails
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        Subject = Email.Offset(, -19) & " - System Update"
        With OutMail
            .To = Email.Text
            .CC = ""
            .BCC = ""
            .Subject = Subject
            .Body = Msg & Chr(10) & Chr(10) & Signature
            '.Display   'or use .Send
            .Send
        End With
        Email.Offset(, -1) = "Done"
    Next Email
End With

Problem, I'd like to set the "Save Sent Message To:" option on each of these emails, too, to file this email into a specific archival folder in Outlook. Is there a way to do this in Excel VBA? I could just copy all these emails manually out of the SENT EMAILS folder, but if it's possible to file them using the macro, even better.


Bonus question: Is there any way to add an existing Signature in Outlook to these emails? I have a default signature that occurs automatically on all NEW emails I create in Outlook, but this VBA method of creating an email does not get that default signature, seems to skip that. In the code above I'm using another signature I created for the macro, but it's not the cool formatted signature I have usually in Outlook.




(also posted here with no response so far...)

I think the answer to both is the same - create a template with the signature and save options that you want and save it to a folder, then use OutApp.CreateItemFromTemplate to create the messages.

I wouldn't create a new outlook instance for each email:

With ActiveSheet
 with createobject("outlook.application")

  For Each Email In .columns(20).SpecialCells(xlFormulas, 2).offset(1).SpecialCells(xlFormulas, 2)
   with .CreateItem(0)
    .To = Email.Text
    .Subject = Email.Offset(, -19) & " - System Update"
    .Body = replace("All,~~As part of normal support and maintenance, this week we updated your store to v" & .[X1] & "~~Any questions please contact me directly.","~",chr(10))
    .Send
   End With
   Email.Offset(, -1) = "Done"
  next

  With .getnamespace("MAPI").getdefaultfolder(4)
   For Each it In .items
    it.Copy
    it.Move .folders("archive")
   Next
  end with

 End With
End sub

In this example I made a subfolder of PostOUT, name "archive".
Outlook has a curious way of 'copying' an item: first 'copy', then 'move'

For he signature I second RO's suggestion to use an outlook template.

Well there you go! Always something new to learn. The CreateItemFromTemplate resolved it all, perfect! The finished code also incorporates SNB's suggestion to move the App to a single instance:

Option Explicit

Sub EmailUpdates()
Dim OutApp  As Object
Dim OutMail As Object
Dim Emails  As Range
Dim Email   As Range
Dim Subject As String

If MsgBox("Process the Activesheet?", vbYesNo, "Proceed?") = vbNo Then Exit Sub

With ActiveSheet
    Set Emails = .Range("T2", .Range("T" & .Rows.Count).End(xlUp)).SpecialCells(xlFormulas, 2)
    Set OutApp = CreateObject("Outlook.Application")
    
    For Each Email In Emails
        Set OutMail = OutApp.CreateItemFromTemplate("C:\Documents and Settings\Jerry\Application Data\Microsoft\Templates\Monetra Upgrade Completed - VP2.oft")
        Subject = Email.Offset(, -19) & " - Monetra Updated"
        With OutMail
            .To = Email.Text
            .Subject = Subject
            .Display
            '.Send
        End With
        Email.Offset(, -1) = "Done"
    Next Email
End With

End Sub

Now, if I could just get around the popup warning in Outlook for each of these emails trying to send themselves, I'd be in Nirvana. Everything I find on the internet is just too convoluted. Easier to just to press YES for each of the emails, for now.

Thanks guys.

Unless you prefer to dive into the registry and it's outlook security settings you can install ClickYes.exe, that is to be downloaded here:
http://www.contextmagic.com/downloads.htm

Besides: your code doesn't do anything on 'archiving' (one of your questions ? see this thread's title). See my previous, amended suggestion.

The TEMPLATE I'm using already has the "Save Sent Messages To:" folder selected properly. Every time I use the macro in that incarnation above the emails file themselves away properly. Thank again, all.