On a worksheet called "Contact Info" column A starting in row 2 I have a list of names (variable length). In Columns B2-D I need the email address, work phone number, and cell phone number.
View 5 RepliesI am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
I use the following code to read information from the Global Address Book in Outlook, and it works. Do any of you know how I can read the Email-address and alias-Name as well? By other word more information from each record. The code below will give me the name only.
View 4 Replies View RelatedI have an Excel form that requires the user to enter the name of their manager. Rather than entering the names manually I would like to use the Outlook address book but I've run into a dead end due to my limited knowledge.
Sub GetOutlookAddressBook()
Set appOutlook = CreateObject("Outlook.Application")
Set CDOSession = appOutlook.CreateObject("MAPI.Session")
CDOSession.logon "", "", False, False, 0
Set Recipients = CDOSession.addressbook(Nothing, "Select Distribution List Members", False, True, 1, "To:", "", "", 0)
I've had a scan through the archives trying to find a post or item on importing Outlook data to Excel, and have so far only found articles on importing data from users' 'Contacts' lists. I need functionality so that when the user selects an office (e.g. 'London') from a list, further details for those offices - which are stored as individual members of the global Outlook addressbook - for example postcode, can be operated upon.
Because this data is relatively dynamic (e.g. potential for relocation of office premises), I need any procedures to refer to a single unified data source, therefore I'd like any VBA procedures to use data directly from the outlook address book, instead of importing a static copy of the data and then performing operations on this.
I have a form in excel (very simple) that I need to put on the web - People can then fill out certiain bits of the information and the idea is that they then click on the button to email it back to me. Ie complete it there and then and email it straight away so that they don't need to save it etc.
I can't for the life of me work out how to do this! I've managed to get the macro to open an email with the correct details on but not paste in the information so am at a complete loss and appear to be going round in circles!
I have data like this:
Sammi Alvarez-Vegan
1201 E. Ponce De Leon Blvd.
Coral Gables, FL 33134
Angie Penns
6870 SW 44th Street
Building G28- #110
Miami, FL 33155
Some names and addresses are 3 lines and some are 4 lines. All have a blank line between. There is a double blank between different name blocks.
I tried to Concatenate so all parts would be in B1 cell. =A1&","&A2&","&A3&","&A4&","&A5&","&A6&","&A7
That did what I wanted for B1, and when I copied it down, B2:B8 was part of an address.
It was not until B9 did the actual next name come into play.
I know there must be a better way
I am using WindowsXP and Excel2007
In Sheet 1 I have the Raw Data
with column headings
Name , Address , Station, Work Phone, Home Phone Mobile number
How can I make an address book in Excel
Suppose in the AddressBook Sheet
in A1 if we write the letter A the names starting with A will be displayed in Cell A2 Downwards along with the retreived information abt that recoreds Similarly In G1 if we write B then the recored starting with letter B should be retreived from the sheet 1 containing raw data in cell G2 downwards and this goes on in column M1=C S= D and so on
New to VBA, so here we go. I want to make address book with userform,is it possible with my example?
View 4 Replies View RelatedI am trying to take an address book on the internet and copy/paste it into excel- not hard right? Ok, so that is done. The problem I have is that there are over 2000 addresses- and they all copy straight down into one column. I know how to transpose the information- but come on- there are over 2000. that seems like a LOT of work. So my first question would be is there a way to do this all at once? For each company/name/address/phone I need one row per company.
View 9 Replies View RelatedI have created a simple address book in Excel 2007, but it is not reader-friendly: My header row in Sheet1 consists of cells labeled “Category”, “Company Name”, “Street Address”, “City, State, Zip”, “Contact Person”, “Office Number”, “Cell Number” so the data for each company is listed in a single row. I would like to see if there’s a way that I can setup the workbook such that the data from Sheet1 automatically reformats into a directory-like format in Sheet2. Ie:
Company Name1 Contact Person1
Street Address1 Office Number1
City, State, Zip1 Cell Number1
Company Name2 Contact Person2
Street Address2 Office Number2
City, State, Zip2 Cell Number2
etc
Also, I would like to figure out a way to filter by the Category column, so that Sheet2 contains all items on Sheet 1 that are labeled Category A, Sheet3 contains all items on Sheet 1 that are labeled Category B, etc.
I'm trying to modify.
The code is this:
Dim finder As Object
Private Sub ComboBox1_Change()
Set finder = Sheets("Sheet1").Columns("A:A").Find(what:=ComboBox1.Text, lookat:=xlWhole)
Main_recipients.Value = finder.Offset(0, 1)
End Sub
Private Sub Send_email_Click()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Now, everything works fine until it gets to this line:-
MailDoc.SEND 0, Recipients
when I get an error message that states "Unable to send mail, no match found in Name & Address Book(s)"
In Lotus, I created a list of recipients (including any additions after the name) and copied that into the cell that feeds the text-box "Main_recipients".
The odd thing is, if I just email myself without any additions after my name it's fine. It's when I try to e-mail multiple people (with and without additions) that it falls over.
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies View RelatedI have specified the email addresses in the code to whom the email will be sent when the user presses "Send Email" button but now I want to add all the addresses in the Access table and write down the code that will send an email to those people whose addresses are in the Access table.
[Code] .....
I am using this peice of code from another post in order to fill a ListBox with Outlook addresses. It works fine, but the the list has thousands upon thousands of names and it takes forever to run. I thought I'd add code to only add specific entries based on what a user inputs to a TextBox, but it doesn't seem to speed-up the process. how I can get to the user-defined data more quickly?
Private Sub CommandButton2_Click()
Dim olApp As Outlook.Application
Dim olNamespace As Outlook.NameSpace
Dim olItem As Outlook.AddressEntry
Dim olAddressList As Outlook.AddressList
Dim olAddressEntry As Outlook.AddressEntry
I'm using Excel 2010 and I have a copy of an address in Excel. I'm trying to identify all the people who live by themselves- without partners and spouses.
I have a column array like this:
A B C D E F G H I J K L M N O P Q R S T
PDENOStatusTitleFirst NamesInitialsSurnameSuffixDate of AttainmentFranchise FlagAddress 1Address 2Address 3Address 4Address 5Address 6Address 7Address 8Address 9Postcode
And the relevant columns is usually L. I am trying to figure out the formula that allow me to extract or at least highlight the people who live by themselves- or alternatively the people who live in the same house (which would therefore identify by exclusion the singletons).
I have the below code to email a specific sheet to an email address, however the email stays in the outbox and isn't sent. Is there something missing from the code or is it a setting issue with my email? I'm using Outlook 2010.
[Code]....
I am trying to send an automated email by use of a "email" button. What I want it to do is to pop up a input box that will ask me who I want to send the email to, and once I hit ok it will send open up outlook and send the email. I have the code to work if I want it to be sent to a specific email address, but I can't seem to get the email address entry part to work. I will attach my code as it lays right now.
Private Sub CommandButton2_Click()'Need to reference: Microsoft Forms 2.0 Object LibrarySet OutApp = CreateObject("Outlook.Application")OutApp.Session.LogonSet OutMail = OutApp.CreateItem(0)strbody = "This is the most up to date copy of EAS Tracking 2.0 as well as the Resource Planning Sheet."attachmnt2 = "C:My DocumentsResource Planning Sheet_External.xls"On Error Resume Next'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|? BELOW IS WHERE I CAN'T GET TO WORK!!!With OutMailDim range As Longrange = Application.InputBox("How many copies do you want?", "Number of Copies").To = range.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt2).Display.SaveEnd With'__________________________________________________attachmnt3 = "C:My DocumentsReport DataWork Request Tracking Data FolderEAS Request 2.0.xls"On Error Resume NextWith OutMail.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt3).Display.SaveEnd .........
I have an Excel 2013 address book that has extra spaces between first names, last names and middle initials all in 1 cell.
Is there an easy way to remove all the spaces between these components?
I need to email a page from a worksheet to a series of people and am currently using the following
Worksheets("Report").Activate
ActiveWorkbook.Save
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
however, I also need the worksheet to be email to the person currently running the macro (i.e. when they email it to the others, a copy is sent to thier own inbox too), is there any way in which this can be done?
The user's email address could be formed from data in the spreadsheet, if there is an easier way to do this (i.e. email address is based on a cell in the spreadsheet).
It worked well at the time and continues to work well.
The problem I have is that I've copied it for use in a new workbook and for some reason it's not picking up email addresses and storing them in the string "stemails", so that when the email is created there are no addressees. Column BA does have email addresses in it however.
[Code] .....
I have a list of email addresses. Most of them are in the format of: firstname.lastname@domain.com
There are about 200 of them. I wish to extract the firstname and lastname into seperate columns so i end up with:
Code:
A | B | C
--------------------------------------------------------------
Firstname | Lastname | firstname.lastname@domain.com
I am sure I came across something before that did something like this. but I was not able to find it. Any macro or script to perform this extraction.
note that some of the addresses are in the format: name@domain.com In those cases I would like just everything before the @ put into column A. I will then manually figure out how the value is to be broken up into firstname / lastname.
sort out the email address as per the company name using Vlookup or any other formulae other than using text to column on "Email Adress" and then doing a Vlookup from the reference table.
Reference table:
Email AddressDomainCompanyxyz@gmail.com@gmail.comGmailabc@hotmail.com@hotmail.com
HotmailAlpha@facebook.com@facebookFacebook
Result Required
Email AddressCompanyxyz@gmail.comGmailabc@hotmail.comHotmailAlpha@facebook.comFacebook
I have a combined sentence with email address at the end. There is a space between email and other part. For example, in Cell A1, it's: PO Box 132, Washington Ave, dennis789@yahoo.com. In cell A2, it changes to: 12 DW Road, georgeyiui@hotmail.com. How can I separete these emails out?
View 7 Replies View RelatedI have a list of email addresses. Most of them are in the format of:
firstname.lastname@domain.com
There are about 200 of them. I wish to extract the firstname and lastname into seperate columns so i end up with:
A | B | C
--------------------------------------------------------------
Firstname | Lastname | firstname.lastname@domain.com
I am sure I came across something before that did something like this. but I was not able to find it. Please let me know if you can provide a macro or script to perform this extraction.
Please note that some of the addresses are in the format: name@domain.com
In those cases I would like just everything before the @ put into column A. I will then manually figure out how the value is to be broken up into firstname / lastname.
I am currently trying to automate a receipt system. I have successfully managed to create an email based on calculated Excel data. The email is created using the following routine.
callref="12345"
strbody = "The main text of the email"
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "John Smith"
.BCC = ""
.Subject = "Test email receipt - " & callref
.Body = strbody
.Display 'or .Send - still developing!!!
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
My problem is that there will be several people using this system so I would like to include a reply address which is different from the sender. Alternatively, I would like to send the email from a different account (but I have to assume that the users will have their own email accounts open).
Is it possible to create a rule that every time I type my name, for my e-mail address to pop up in excel instead?
For example, if I type "purple1686", I want "12purpleexample@domain.com" to show up as a hyperlink to my default e-mail program.
I am also curious if I was to write "my website" is it possible to make it a hyperlink to "www.purple1686example.com"?
Those two first may be easy to answer, so here is the challenging part for me:
Can I make that happen as a rule on every spreadsheet I open?
I don't know if I should make a template, or use a formula? The ideal scenario would be if there was a way to do it through conditional formatting.
I have used conditional formatting to change the color of a word every single time I open a new template and paste the words into it, or even by typing them out.
I've two columns, one being the first name and the second being the last name. Is there a way that I can convert these two columns into the correct format for the email address ? Rather than having to manually do this.
View 4 Replies View RelatedI have quite a number of names & email addressees that I received by email.
I wish to import them into excel, what is the best way to do this ?
Preferably I would like the names in one column and email addys in another.
When I type a eMail address, such as: " myemail@hotmail.com " into any cell and then touch enter, it automatically becomes a hyperlink coloring it blue and underscored. I do not want it to be a hyperlink, for when it is a hyperlink, I have to click on remove hyperlink.
View 2 Replies View Related