Personal Macro workbook sending emails Macro Problem

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

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

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19
This worked!! I have been trying for almost 3 weeks to fix this error. Thanks for you help.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top