VBA Code, attach worksheet from different file every week

[hubbakong]

Hi there, I hope I can put this right.

I have found a VBA that creates an email and attaches files from the same folder. The thing is that I want to attach yet another file from a folder that changes name every week (we create a new file and new worksheet every week via a macro, I cannot edit this otherwise I could you save the file in the same folder as the other files) so the foldername is dynamic, ex "folder V19" and next week "folder V20".

Private Sub CommandButton1_Click()
'
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)
For i = 2 To 2
to_emails = to_emails & Cells(i, 1) & ";"
cc_emails = cc_emails & Cells(i, 2) & ";"
Next i
myMail.To = to_emails
myMail.CC = cc_emails

myMail.Subject = "Test!"
myMail.Body = "Test!"

For i = 2 To 4
source_file = "C:\xxx" & Cells(i, 5)
myMail.Attachments.Add source_file
Next i
For i = 5 To 5
source_file = "C:\xxx" & Cells(i, 5) (this is where my problem starts since the name changes each week)
myMail.Attachments.Add source_file
Next i
myMail.Display
'
End Sub

As I am writing this I realize that my English got really bad and I hope that you can understand my question.

Thank you in advance!

 

[hubbakong]

Hi there, I hope I can put this right.

I have found a VBA that creates an email and attaches files from the same folder. The thing is that I want to attach yet another file from a folder that changes name every week (we create a new file and new worksheet every week via a macro, I cannot edit this otherwise I could you save the file in the same folder as the other files) so the foldername is dynamic, ex "folder V19" and next week "folder V20".

Private Sub CommandButton1_Click()
'
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)
For i = 2 To 2
to_emails = to_emails & Cells(i, 1) & ";"
cc_emails = cc_emails & Cells(i, 2) & ";"
Next i
myMail.To = to_emails
myMail.CC = cc_emails

myMail.Subject = "Test!"
myMail.Body = "Test!"

For i = 2 To 4
source_file = "C:\xxx" & Cells(i, 5)
myMail.Attachments.Add source_file
Next i
For i = 5 To 5
source_file = "C:\xxx" & Cells(i, 5) (this is where my problem starts since the name changes each week)
myMail.Attachments.Add source_file
Next i
myMail.Display
'
End Sub

As I am writing this I realize that my English got really bad and I hope that you can understand my question.

Thank you in advance!