Find a cell value in excel using outlook vba

Status
Not open for further replies.

shas1928

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Dear All,

Greetings,

I would like to know is there any way to find a cell value ( which is an email address) from an excel file corresponding to my email subject or an input value ( which is employee ID) using outlook vba so that whenever creating an email, I can avoid searching for the specific recipient email address each time in the excel sheet which contains two columns (employee ID and email addresses).

Thanks for help & support.

regards
shas
 

shas1928

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Dear All,

I am little disappointed since I haven't got a reply so far ( hope my question is not a senseless). but it made me keep digging.
so I found a solution and happy to share it in case it may helps someone like me.
here is the code and have a nice day.

Sub copyEmailFromExcel()
On Error GoTo ErrorHandler

Dim empEmail As String
Dim emailTo As Outlook.Recipient

Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = False
.EnableEvents = False
End With
strFile = "C:\Users\.........\Desktop\staffemails.xlsx" 'Put your file path.
Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)

Dim SearchRange As Range
Dim employeeID As Long
Dim lngLastRow As Long
Dim strRowNoList As String

employeeID = InputBox("Please Enter Employee ID") 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Search Column A, change as required.
For Each Cell In Range("A2:A" & lngLastRow) 'Starting cell is A2, change as required.

If Cell.Value = employeeID Then 'check the value matches
strRowNoList = strRowNoList & Cell.Row
End If
Exit For
Next Cell

If strRowNoList = "" Then
MsgBox ("No emails found")
Exit Sub
End If
empEmail = Cells(strRowNoList, 2)
sourceWB.Close False

Dim objMsg As MailItem
Set objMsg = Application.ActiveInspector.CurrentItem
Set emailTo = objMsg.Recipients.Add(empEmail)
emailTo.Type = olTo
emailTo.Resolve
objMsg.Display


Exit Sub
ErrorHandler:
' Insert code to handle the error here
MsgBox ("Invalid ID or Unknown Error")
End Sub
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
K Use VBA to find Sender and Recipient from Microsfot 365 Journaled Email Items Outlook VBA and Custom Forms 3
M How can we find the list of users who are members of a deleted distribution list? Exchange Server Administration 2
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
H Outlook 2003 find by "has attachment" Using Outlook 1
geoffnoakes Find Contacts with UDFs "in this item" Using Outlook 1
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
B Outlook 2010 Can not find a certain file in M/S Outlook 2010. Using Outlook 1
T "cannot find the calendar folder for this item" - calendar items stuck in outbox Using Outlook 0
B Looking to filter (or just find/search) for only messages that the sender has sent more than 1 messa Using Outlook 2
B Search: Cannot find which Folder Contains a Message Using Outlook 3
K How to find specific header and copy the mail body Using Outlook 0
Hudas VBA find and open an email without looping thru each email in the inbox Outlook VBA and Custom Forms 1
E How to find or recover Lost e-mails within a folder under Inbox? Using Outlook 2
H "Advanced find: column for full folder path? Using Outlook 1
M What is the best way to find all records of an e-mail for our company? Outlook VBA and Custom Forms 2
C Find all deleted recurrence appointments Outlook VBA and Custom Forms 4
mrje1 Is there a Find and Replace feature in Outlook 2016? Using Outlook 4
G Can't find contacts entered Using Outlook 0
Connie Boyer Exporting Outlook calendar/find next repeating meeting Using Outlook 3
B Find related messages to sender Outlook VBA and Custom Forms 7
B Find Related Emails to sender Using Outlook 1
RBLampert Outlook "can't find" my e-mail server to receive messages Using Outlook 22
M Can't find messages Using Outlook 4
Diane Poremsky Find the Distribution Lists a Contact Belongs to New Slipstick.com Articles 0
T Macro to find contacts by category and copy them to another folder Outlook VBA and Custom Forms 15
thomas zaleski Cannot find the Sending FOlder Using Outlook 2
J Find and Highlight text in outlook Outlook VBA and Custom Forms 4
J Outlook Mailbox cleanup; any way to change limit to find items older 999 days? Exchange Server Administration 2
F Disable "Find related messages" Using Outlook 1
Jennifer Murphy Can't delete or even find task attachment Using Outlook 1
K Re: Use Instant search to find messages from a contact Outlook VBA and Custom Forms 6
Andrew Hawkins "Advanced Find" Using Outlook 1
M BCM 2007 Can't find SS2012Express Using Outlook 2
O Outlook 2013 contacts - Where to find "Display as"-field Using Outlook 2
A Advanced find query facilty Help please Using Outlook 2
C Cant find emails after moving pst Using Outlook 1
G Find Organizer of appointment Using Outlook 2
R writing "Instant Search" queries to find User-Defined fields Using Outlook 0
P Outlook Advanced Find Using Outlook 0
S Advanced Find text field limits Using Outlook 2
C O2010: Using Advanced Find to locate exact queries Using Outlook 3
D Outlook 2007 Find All Related and Messages From Sender not working Using Outlook 3
V Advanced Find - [Today]-3 ? Using Outlook 4
S Cannot Find Shared Database on Remote Computer BCM (Business Contact Manager) 13
V rule to Find some words in the subject, change subject then forward Using Outlook 1
L Mapi folder.FIND: error Using Outlook 0
T Find the sender who sent the mail from Generic Mailbox. Using Outlook 4
S Can't find some addresses after a contact list export. Using Outlook 1
F Can't find all stationery listed in stationery picker Using Outlook 2
J Mass find & delete email addresses from address book. Using Outlook 3
Similar threads


















































Top