Excel macro to send emails using template in Ms Word

In our earlier posts, we explained how to recreate mail merge functionality using vba in word to equip with few additional features.

In this post, we are developing same logic in excel. Here's a sample code. You can tweak it as required. And feel free to write to me in case you face any trouble while tweaking.


Considering the word document template as:

And data in excel as:

Sub Word()

Dim wd As Word.Application

Dim olApp As Outlook.Application

Dim Omail As Outlook.MailItem

Dim wddoc As Document

Set wd = New Word.Application

Set wddoc = wd.Documents.Open("C:\ABC.docx")

For i = 2 To 5

Set olApp = New Outlook.Application

Set Omail = olApp.CreateItem(olMailItem)

 With Omail

     .To = "abc.def@xxx.com"

     .Subject = "I love XtremeExcel's Youtube Channel"

     .Body = wddoc.Content

 For x = 1 To 2

 .Body = Replace(.Body, "_" & x & "_", Sheets(1).Cells(i, x).Value)

 Next

 .Send

 End With

Next

End Sub

Excel macro to get data from last modified file in a folder

Question:

I was hoping you might be able to help me with a macro I have created for a project I am working on. I have only pieced together a macro  to look in a specific folder, then with the .xlsx files in that folder copy specific cells and paste them in to the specified cells in the 1st empty row of another workbook. But the issue I keep running into is I want the macro to start with the .xlsx file with either the newest date modified or the oldest date modified. As it is now by default, the macro is going alphabetically. I didn't enter anything into the code for it to do this but I'm not sure how to make it look at the date modified instead.

Do you think you might be able to help? I would appreciate it very much.

Answer:

Dim fso As Scripting.FileSystemObject
Dim fol As Scripting.Folder
Dim fdr As Scripting.Folder
Dim fil As Scripting.File
Dim flc As Scripting.Folders

Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder("YourPathName")
Set flc = fol.SubFolders

For Each fdr In flc
  For Each fil In fdr.Files
        Debug.Print fil.DateLastModified
  Next fil
Next fdr

Set fso = Nothing
Set fol = Nothing
Set flc = Nothing