VBA Code, attach worksheet from different file every week

hubbakong

New Member
Joined
May 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

hubbakong

New Member
Joined
May 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
1620986743363.png
 

Forum statistics

Threads
1,148,364
Messages
5,746,275
Members
424,003
Latest member
paaskanama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top