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.
Similar threads
Thread starter Title Forum Replies Date
witzker How to find all emails from and to a contact in OL 2019 Using Outlook 1
S How to find emails that I sent that have not received a reply? Using Outlook 0
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 Using Outlook 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

Similar threads

Top