need some help with automated emailing

[billyheanue]

Hi all,
I'm hoping someone with some VB skills can take a gander at my code, and help me get these emails to send with an excel attachment on them.

Sub vendorRep_massemail()

row_number = 1


Do
DoEvents
    row_number = row_number + 1
    Dim infoprompt As String
    Dim firstname As String
    Dim companyname As String
    
    firstname = Sheet2.Range("B" & row_number)
    infoprompt = Sheet2.Range("Q2")
    companyname = Sheet2.Range("E" & row_number)
    
  infoprompt = Replace(infoprompt, "replace_name_here", firstname)
  infoprompt = Replace(infoprompt, "company_replace", companyname)
  'How do I get the emails to send with a "RFI" excel sheet attached?
  'For example, this sheet would be saved at the location C:\test.xlsx on my computer
  
  
  
  Call SendEmail(Sheet1.Range("A" & row_number), "Action Required: Automated Ordering on Behalf of Steward Central Purchasing", infoprompt)
  
  Loop Until row_number = 10 'how do i make this so it loops until the list in col. A ends?
  
    
    
End Sub

Thank you!
Bill

 

[Trevor G]

What code are you using in the Call SendEmail?

 

[billyheanue]

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.Send




End Sub

Hi Trevor,

This is what ive got

 

[Trevor G]

Add the line below the comments for the attachments

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
'Add this line
olMail.Attachments "The address to attach the file goes here"
'Check to see if it works by using this line first
olMail.Display
'Once checked uncomment this line
'olMail.Send

 

[billyheanue]

Hey trevor, thanks for the help!

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.Attachments.Add "I:\Westwood Operations Team\Individual User Folders\Bill\Reptrax\test attachment.xslx"




olMail.Send




End Sub

Could you possibly check whats wrong with my source of the document / how am I supposed to format this as code?

Getting runtime error that it cannot verify the file extension and am wondering if i should just try saving it on my desktop

 

[Trevor G]

What is the error message stating in full? Try keeping the

OlMail to show Display until it has been checked within your outlook then reset it to OlMail.Send