-
Vba code not working
Hi all,
this function allow to send by email all the files presents in a folder.
Compiling the function the result is myFiles As Outlook.File not defined from user. Can u help me to fix?
Thanks!!!!
Code:
Sub SendExcelFiles()
'Declare variables
Dim myFolder As Outlook.Folder
Dim myFiles As Outlook.File
Dim myEmailAddress As String
Dim mySubject As String
Dim myCell As Range
'Set the folder variable
Set myFolder = Application.GetFolder("C:\Users\[Your User Name]\Desktop\Excel Files")
'Get all the files in the folder
Set myFiles = myFolder.Files
'Loop through the files
For Each myFile In myFiles
'Get the email address from the cell
Set myCell = myFile.Sheets(1).Range("A1")
myEmailAddress = myCell.Value
'Get the subject of the email
mySubject = myFile.Name.Replace(".xlsx", "")
'Create a new email message
Dim myMail As Outlook.MailItem
Set myMail = Application.CreateItem(olMailItem)
'Set the sender's email address
myMail.SenderEmailAddress = "[Your Email Address]"
'Set the recipient's email address
myMail.To = myEmailAddress
'Set the subject of the email
myMail.Subject = mySubject
'Set the body of the email
myMail.Body = "This is an email message with an Excel file attached."
'Attach the file to the email
myMail.Attachments.Add myFile
'Send the email
myMail.Send
Next myFile
End Sub
-
Welcome to VBAX AndyonWeb. I have enclosed your supplied code with code tags (See first line in my signature for a hint). I also closed up the code as we try not to waste whitespace on this forum. Maybe try this version instead
Code:
Sub Email_All_Files_in_Folder()
Dim OutApp as Object
Dim OutMail as Object
Dim strBody as String
Dim myFldr as String
Dim myFile as String
Set OutApp = CreateObject ("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
myFldr = C:\Users\YourUserName\Documents\FolderName\SomeFiles\"
myFile = Dir(MyFldr)
strBody = "<BODY style = Font-Size: 14pt; Font-Family: Arial>" & _
"Hi Team <p> Please see file(s) attached. <p>" & _
"Thanks, <br> Your Name"
On Error Resume Next
With OutMail
.To = "Somebody.com"
.cc = " "
.bcc = " "
.Subject = "Daily file(s) " & Format (Date, "mm/dd/yyyy")
.Display = " "
.htmlbody = strBody & .htlmbody
Do while <> " "
.Attachments.add myFldr & myFile
myFile = Dir
Loop
End with
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
-
Thank you for your suggestion. I notice that the function has changed completely and so configured it no longer does what I need. I also tried to use yours but no mail is sent. Thanks anyway for trying.
