Variable not defined (VBA)?

[Jyggalag]

Hi all (please scroll to the bottom for the question),

I am currently trying to use the following code to send out word documents through a vba macro (source: Using Excel VBA to Send Emails with Attachments - wellsr.com):

In code format:

Sub AttachMultipleFilesToEmail()
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

For i = 2 To 5
    source_file = "C:\Work Files\" & Cells(i, 3)
    myMail.Attachments.Add source_file
Next i
End Sub

I have tried to change this a bit so it fits my own code, where I already dimmed a lot of it prior to finding this code. I have therefore written:

Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application

Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)

My issue now is that whenever I try to run the code, it says the following:

Does anybody know how I can fix this issue?

Would be very much appreciated!!

Thank you all

Kind regards,
Jyggalag

 

[Rick Rothstein]

You are using a variable named "i" in your code but you did not Dim it. Add this line of code to the beginning of your macro and the error should go away...

Dim i As Long

 

[Jyggalag]

You are using a variable named "i" in your code but you did not Dim it. Add this line of code to the beginning of your macro and the error should go away...

Dim i As Long

Thank you very much Sir. !

I used this code now and it works:

Sub send_email_complete()
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dim source_file, to_emails, cc_emails As String
Dim i, j As Integer

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

For i = 2 To 10
    to_emails = to_emails & Cells(i, 15) & ";"
    cc_emails = cc_emails & Cells(i, 16) & ";"
Next i


For j = 2 To 2
    source_file = "\\Companyxyz\userdata\t684895\home\Documents\faq folder\" & Cells(j, 17)
    myMail.Attachments.Add source_file
Next

ThisWorkbook.Save
source_file = ThisWorkbook.FullName
myMail.Attachments.Add source_file

myMail.CC = cc_emails
myMail.To = to_emails
myMail.Subject = "Files for Everyone"
myMail.Body = "Hi Everyone," & vbNewLine & "Please read these before the meeting." & vbNewLine & "Thanks"

myMail.Display


End Sub

However, I have two follow-up questions, if I may:

When I press the macro now, this happens:

My issue is that it attaches the excel file to the email, but I only want it to attach the word file. Can you (or anybody else) spot what I should change in my code?

In addition to this, it would be nice if I could add a code that simply sends the email without having it pop up for approval to be sent in outlook, if anybody knows how?

Thank you so much!

 

[Jyggalag]

I fixed it! So sorry

Here is the full code if anybody wants to do something similar (note that I have my emails in column O, CC emails in column P and files to attach in column Q, hence the index numbers 15, 16 and 17):

Sub send_email_complete()
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dm source_file, to_emails, cc_emails As String
Dim i, j As Integer

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

For i = 2 To 10
    to_emails = to_emails & Cells(i, 15) & ";"
    cc_emails = cc_emails & Cells(i, 16) & ";"
Next i


For j = 2 To 2
    source_file = "\\Company.FILE.COMPANY.NET\userdata\t395824\home\Documents\faq folder\" & Cells(j, 17)
    myMail.Attachments.Add source_file
Next

ThisWorkbook.Save
source_file = ThisWorkbook.FullName

myMail.CC = cc_emails
myMail.To = to_emails
myMail.Subject = "Files for Everyone"
myMail.HTMLBody = "Dear all,<br/>" & "<BR>" & _
"texttexttext ""text"") texttexttext.<br/>" & "<BR>" & _
"texttexttext.<br/>" & "<BR>" & _
"text,<b><u> texttexttext</b></u> texttexttexttexttext.<br/>" & "<BR>" & _
"texttexttexttexttext.<br/>" & "<BR>" & _
"texttexttexttexttext.<br/>" & "<BR>" & _
"texttexttext.<b> texttexttext:<br/>" & "<BR>" & _
"<b>-</b> texttext<br/>" & _
"<b>-</b> texttext<br/>" & _
"<b>-</b> texttexttext<br/>" & _
"<b>-</b> texttexttext<b><u> texttexttext</b></u>.<br/>" & "<BR>" & _
"texttexttext<u> texttext@texttext</u><br/>" & "<BR>"

myMail.Send
i

End Sub