Personal Macro workbook sending emails Macro Problem

[kiwikiki718]

I created a personal macro that would allow me to automatically send emails using Excel. The macro works fine if I am using the actual workbook where the macro was created but not on new workbooks. I believe the macro is generating the fields to search base on the work book where the macro was created. so for example if there are 2 entries in the workbook where the macro was created and 5 entries with the new work book, the macro will only process 2 out of the 5 emails. I am not sure what I am doing wrong. here is the coding I have:

Sub SendMail()
'
' SendMail Macro
' created 4-5-17

Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure you want to send Emails?", vbYesNo, "Send Emails")
If Answer = vbYes Then

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
For I = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Set olApp = CreateObject("Outlook.application")
Set olMail = olApp.CreateItem(olMailItem)


With olMail
.To = Cells(I, 1).Value
.Subject = Cells(I, 2).Value
.BodyFormat = olFormatHTML
.HTMLBody = Cells(I, 3).Value
.Attachments.Add (Cells(I, 4).Value)
.SendUsingAccount = Outlook.Session.Accounts.Item(1)
.Display
''.Send
End With

 

[MARK858]

The Personal Workbook is a different workbook book so you can't use the sheets codename, you need to use the sheetname so try changing Sheet1 in the below to

Sheets("YourSheetName")

where YourSheetName is your sheets name as it appears on the sheets tab.

For I = 2 To [COLOR="#FF0000"]Sheet1[/COLOR].Cells(Rows.Count, 1).End(xlUp).Row

 

[kiwikiki718]

This worked!! I have been trying for almost 3 weeks to fix this error. Thanks for you help.

 

[MARK858]

You're welcome