PDA

View Full Version : Vba code not working


andyonweb
07-22-2023, 01:21 AM
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!!!!


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
Aussiebear
07-22-2023, 04:15 AM
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



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
andyonweb
07-23-2023, 12:38 AM
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.