Automating an Excel Template using VBA and Outlook

I have two columns that I am trying to reference to spit out multiple values for the possible outcome of two separate input values. For Example:
A         B                   C
SCAC      Linear Feet         Weight
PITD              20                  10000
ESTE              10                  15000
SAIA              25                  20000
ABFS             20                  25000

Input Values

Linear Feet     Weight
20                     20000

If the entered field is greater than or equal to I want it to return all values that match the criteria
Sorry guys, I just word from my boss who wants the INPUT values to be either/or a match for LINEAR FEET or WEIGHT...any ideas to accommodate this new ask?

Return
SCAC Code
PITD    
SAIA
ABFS

What's the best way to write this formula?


      I am trying to build a formula that reference cells in a table. My goal is to only return values that have data in the reference cells. So if a cell in the table is blank don't return it. Also it is not referencing the first row in my table. Any ideas on how to fix?

=IFERROR(INDEX('Carrier Rules and Contact Info'!$A$A,SMALL(IF(('Volume Template'!$B$4>='Carrier Rules and Contact Info'!$B:$B)+('Volume Template'!$C$4>='Carrier Rules and Contact Info'!$C:$C),ROW('Carrier Rules and Contact Info'!$B:$B)),ROW('Carrier Rules and Contact Info'!2:2))),"")

Open in new window


How can I improve this formula?



Add Checkboxes that will return emails addresses in the BCC field into Outlook
In desperate need of help


I am building an email in Excel for Outlook using the following code:

I want to use check boxes (using the form controls) to decide whether an email is used.

If the check box reference cell is true input the value of a cell (an email address) into the BCC field.

If Cell H4=True return Cell F4.

Going down a list of about 30 entries so this needs to loop until there are no more check boxes

Sub Mail_Selection_Range_Outlook_Body()
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a fixed range if you want
Set rng = Sheets("Volume Template").Range("K4:L14").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC =
.Subject = "UTS VOLUME QUOTE REQUEST"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Open in new window


How do I adjust the code below to accomplish this task? I am very new and still learning.
Volume-Project.xlsm
Josh HouseDigital Sales CoordinatorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
I believe this question needs to split. 1 for Excel formula and one for Outlook VBA.
0
ShumsDistinguished Expert - 2017Commented:
For correcting Excel Formula, you can use below Array formula:
=IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),"")

Open in new window

confirmed with Ctrl+Shift+Enter
Hope this helps
I have also corrected your formula for Contact Email & Special Instructions, please find attached.
Volume-Project_v1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Josh HouseDigital Sales CoordinatorAuthor Commented:
I will split this up into 2 questions sorry for any confusion this may have caused
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ShumsDistinguished Expert - 2017Commented:
Does Excel Formula helped you?

If Yes, I would request you to close this question and raise another one for Outlook VBA.
0
Josh HouseDigital Sales CoordinatorAuthor Commented:
Yes, that worked perfectly I will close this question
0
Josh HouseDigital Sales CoordinatorAuthor Commented:
this helped tremendously Thank you
0
ShumsDistinguished Expert - 2017Commented:
You're Welcome Josh! Glad I was able to help.
0
Josh HouseDigital Sales CoordinatorAuthor Commented:
I added the new question as Add Checkboxes that will return emails addresses in the BCC field into Outlook
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.