VBA To Create (But Not To Send) E-mail Drafts Based On Table

CyrusTheVirus

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
749
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Looking for a code that will populate e-mail drafts within outlook, but not send them out, based on information within a table. The table name is Email_Table, and the sheet is Sheet1.

Specifically, what I need to populate within the e-mail is:

1) The subject which is in cell B1.

2) The body, which is in cell B2, but I need the body to be two lines below "Hi (insert first name of supervisor (only one time) from column 1 of Email_Table),".

3) Headers from table (copy/pasted), with corresponding data per supervisor (copy/pasted), but ONLY for table columns 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17... to clarify I need one e-mail to be drafted for each supervisor listing out all of their items, the below example shows some supervisors with 1 item, 2 items, and 4 items. Below is an example of what Sheet1 looks like, and below that is an example of the 4 e-mails that I would want populated (but not sent, so drafted) within outlook.

Can anyone please help with this?


Subject:Outstanding Items
Body:The below item(s) are outstanding. If these are already resolved, then please ignore this e-mail. Also, please let me know if you need assistance with this.
Email_Table
First NameSupervisor E-mail AddressSupervisor NameEmployee NameHourly/SalaryEmployee No.Employee InfoPinLocation No.Location NameDate InTime InDate OutTime OutRequested DateOptionsNotes
AnneAApple@123.orgApple, AmandaSmith, JohnHourly1235Smith, John600009012Accounting2/6/201912:04:26Call In Missing or Not Approved
KelliKBlacksmith@123.orgBlacksmith, KelliRogers, AndreaSalary5168Rogers, Andrea100002000HR2/12/201907:54:55Call Out Missing or Not Approved
GeorgeGCurry@123.orgCurry, GeorgeSalem, TravisHourly54545Salem, Travis301002600Warehouse2/11/201913:21:38Call Out Missing or Not Approved
GeorgeGCurry@123.orgCurry, GeorgeSalem, TravisHourly66464Salem, Travis102009102Warehouse2/12/201913:16:32Call Out Missing or Not Approved
MichelleMMiller@123.orgMiller, MichelleRussell, CoreyHourly848Russell, Corey102009102Warehouse2/12/201912:33:04Call Out Missing or Not Approved
MichelleMMiller@123.orgMiller, MichelleRussell, CoreySalary848Russell, Corey311002802House2/12/201910:15:01Call In Missing or Not Approved
MichelleMMiller@123.orgMiller, MichelleSmith, SeanSalary545Smith, Sean311002802House2/12/201910:15:01Call In Missing or Not Approved
MichelleMMiller@123.orgMiller, MichelleTompkins, BrianSalary949Tompkins, Brian2/12/20193Calendar Request Pending Approval

<tbody>
</tbody>




Hi Amanda,
The below item(s) are outstanding. If these are already resolved, then please ignore this e-mail. Also, please let me know if you need assistance with this.
Supervisor NameEmployee NameHourly/SalaryPinLocation No.Location NameDate InTime InDate OutTime OutRequested DateNotes
Apple, AmandaSmith, JohnHourly600009012Accounting2/6/201912:04:26Call In Missing or Not Approved
Hi Kelli,
The below item(s) are outstanding. If these are already resolved, then please ignore this e-mail. Also, please let me know if you need assistance with this.
Supervisor NameEmployee NameHourly/SalaryPinLocation No.Location NameDate InTime InDate OutTime OutRequested DateNotes
Blacksmith, KelliRogers, AndreaSalary100002000HR2/12/201907:54:55Call Out Missing or Not Approved
Hi George,
The below item(s) are outstanding. If these are already resolved, then please ignore this e-mail. Also, please let me know if you need assistance with this.
Supervisor NameEmployee NameHourly/SalaryPinLocation No.Location NameDate InTime InDate OutTime OutRequested DateNotes
Curry, GeorgeSalem, TravisHourly301002600Warehouse2/11/201913:21:38Call Out Missing or Not Approved
Curry, GeorgeSalem, TravisHourly102009102Warehouse2/12/201913:16:32Call Out Missing or Not Approved
Hi Michelle,
The below item(s) are outstanding withim. If these are already resolved, then please ignore this e-mail. Also, please let me know if you need assistance with this.
Supervisor NameEmployee NameHourly/SalaryPinLocation No.Location NameDate InTime InDate OutTime OutRequested DateNotes
Miller, MichelleRussell, CoreyHourly102009102Warehouse2/12/201912:33:04Call Out Missing or Not Approved
Miller, MichelleRussell, CoreySalary311002802House2/12/201910:15:01Call In Missing or Not Approved
Miller, MichelleSmith, SeanSalary311002802House2/12/201910:15:01Call In Missing or Not Approved
Miller, MichelleTompkins, BrianSalary2/12/2019Calendar Request Pending Approval

<tbody>
</tbody>
 
Last edited:
Sort by date Sort by votes
What msgbox?? Need to see the code.

You might have a look at the locals window, too.
Or google to understand arrays.

Or look at the codes posted.
Or germane to your post,
aFieldsToKeep = Array(2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17)

For i = LBound(aFieldsToKeep) To UBound(aFieldsToKeep)
debug.print i, Columns(aFieldsToKeep(i)
Next i
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
need to fix that last bit of code I posted,

aFieldsToKeep = Array(2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17)

For i = LBound(aFieldsToKeep) To UBound(aFieldsToKeep)
debug.print i, aFieldsToKeep(i)
Next i
 
Upvote 0
Hey Fazza,

What I'm asking is if we take the below example the MsgBox pulls 0, 1, 2 when the array is 3, 5, 7. Of Course it correctly takes columns 3, 5, 7 when referencing, but how does it know to pull 3, 5, 7 when it is being delivered 0, 1, 2 ?

Sub ArrayTest()


Dim i As Long
Dim MyArray As Variant


MyArray = Array(3, 5, 7)


For i = LBound(MyArray) To UBound(MyArray)


MsgBox i


Next i
 
Upvote 0
MyArray = Array(3, 5, 7)

the elements are addressed,
MyArray(0) - which is 3
MyArray(1) - which is 5
MyArray(2) - which is 7

you can see this in the VBE's (Visual Basic Editor) local windows. if you don't know what this is, please google

Also google to understand arrays

I'll repeat my prior post,

[/code]
Rich (BB code): 
aFieldsToKeep = Array(2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17)

For i = LBound(aFieldsToKeep) To UBound(aFieldsToKeep)
debug.print i, aFieldsToKeep(i)
Next i


OK?
 
Upvote 0
Fazza said:
MyArray = Array(3, 5, 7)

the elements are addressed,
MyArray(0) - which is 3
MyArray(1) - which is 5
MyArray(2) - which is 7

you can see this in the VBE's (Visual Basic Editor) local windows. if you don't know what this is, please google

Also google to understand arrays

I'll repeat my prior post,

[/code]
Rich (BB code): 
aFieldsToKeep = Array(2, 3, 4, 5, 8, 9, 10, 11, 12, 13, 14, 15, 17)

For i = LBound(aFieldsToKeep) To UBound(aFieldsToKeep)
debug.print i, aFieldsToKeep(i)
Next i


OK?
Click to expand...

All good. Thank you for your time Fazza.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,826
Messages
6,121,794
Members
449,048
Latest member
greyangel23

Share this page

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
Back
Top