I have come up the code below. It imports contact information from Outlook contacts in a contact folder called Private Contacts. The below code has been copied and modified from this forum.
As it stands the macro creates a new workbook and places the data on that. What I need it to do is create a new worksheet with the name of private compare on the active workbook and then place the data on that worksheet.
I have been trying to use VB to send contacts from contacts.xls to outlook. I have some code from MSDN that shows me how to put in ONE contact (which is already declared in the code). Well I was thinking of writing something to take a worksheet that has x number of contacts and automating x number of contacts. The problem is, I am familiar with using SQL to pull records, but I was thinking there was something easier like importing the excel namespace. So I decided to try it out this way and found this little tidbit of code which is:
Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet xlApp = New Excel.Application xlApp.Visible = False xlBook = xlApp.Workbooks.Open("contacts.xls") xlSheet = xlBook.Worksheets(1)
'READ FILE HERE Dim pXLRange As Object
pXLRange = xlSheet.UsedRange
Dim i = 0 Dim j = 0 For i = 1 To UBound(pXLRange, 1) For j = 1 To UBound(pXLRange, 2) Debug.Print(pXLRange(i, j)) Next Next
It gives me this error for the FOR i=1 to UBound line:
Unable to cast COM object of type 'System.__ComObject' to class type 'System. Array'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
What I've got: This code allows the user to pick one (sub)folder - the MailItem properties of the folder are then printed to the sheet.
My goal: Rather than allowing the user to pick one folder, I'd like to set up a hardcoded array of five subfolders found in a shared inbox. The hierarchy is as follows:
The thing is, I have huge problems with referring to any of the red subfolders above. I think I'm able to point to the shared inbox if I substitute .Pickfolder with this, but then I'm stuck! I need to go down two levels in the hierarchy.
I should be able to produce an array by myself so if I at least just could get a pointer on how to refer to one single folder that would be truly awesome.
Work on a spreadsheet with multiple sheets , that will then be emailed once a month, with different sheets going to different contacts because of our timezone difference.
If a sheet could be used to maintain a list of contacts and which sheet to email out Also in which format PDF, Excel (locked down) or both would be useful.
A button maybe on the contact sheet to start the process / but also with an option to set-up to email on a certain day per month would be good.
the number of contacts at the moment varies between 1 and 4 for each sheet - perhaps , we could set-up to email up to 10 contacts per sheet
The email client is Outlook
Each contact should only receive their sheet and not see any of the other sheets , also the contacts should not be able to alter the spreadsheet at all. hence the possibility of using PDF , but most still would like to see the report in excel format.
there would be about 20 sheets , to go to between 1 and 10 contacts for each sheet
I have attached a dummy workbook , which has 4 example worksheets in
In the real workbook, the worksheet names , also will have spaces in them.
I could setup a Start Sheet and a finish sheet - so that the macro - can go through each worksheet in between , if that offers a solution also a maintenance sheet which has the sheet name , email contacts email address and PDF, Excel or both format.
I have outlook on my PC , so i can play - BUT its not connected via exchange server, if that makes any difference.
I am trying to import outlook calender automaticly using vb. I have found this site http://www.kittytours.org/calendarproject/trouble.html that has the macro ImportCalendarData. I try to run it but I get syntax error at the row
With ActiveSheet.QueryTables.Add(Connection:= Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data
and a yellow arrow is pointing at the top line(sub impo...)
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
I'm in search of a VBA macro which will send a mail through outlook automatically with the count of files(.pdf + .msg files) in any given folder.
I work for an organization, where we use Outlook for mailing purpose.
Everyday Inventory(.pdf files and .msg files) will be saved in a folder that is to be processed.
I want a macro which should automatically send the total inventory (.pdf files + .msg files) through my outlook mailbox(say: email@example.com) to a my superiors outlook mailbox(say: firstname.lastname@example.org).
If the macro sends that email to my superior before me and my superior logon to computers at perticular time (say: 7:00AM daily), need to plan our day at work.
I have to send out emails to all of my staff with their new login ID's and PW's and other misc information one by one to each user. Reason being is that all ID's, PW's and other information pertains to that specific user and includes sensitive information.
I know there's a VBA script I can use to send out the emails, but the script I found online keeps crashing my Outlook and requires it to go through my personal inbox which takes 15 minutes every time.
I have the pertinent information split up into different columns/cells.
A1: email A2: subject A3: body (ID, PW, verbage) A4: attachment link (if required) A5: if I could have Outlook automatically stamp each email with my signature that'd be awesome as well.
How to add an appointment to a shared outlook calendar from Excel. I will be entering shipments in an Excel page and would love to just click a button and have an appointment made. I am very much a novice on how programs communicate with one another. The only information that will need to go on the appointment is the subject, location, and time. We set all our appointments at 15 minutes duration. The code I have so far is this:
The problem is it only adds it to my default calendar. I need to be able to add it directly to the shared calendar in Outlook.
I'm trying to identify a way of setting up a booking system for some meeting rooms we have at work. Always looking for a way of simplifying something that soaks up one of my colleagues time.
I read this post about a golf booking system and it looks like it could be adapted to suit, I can imagine a different worksheet for each room and the fields for entry on choosing the timeslot being the meeting title, who booked and a contact number and the slot showing the meeting name when hovered over possibly.
I noticed a similar thread about Outlook, I know nothing about this sort of stuff within Outlook and not sure it may be possible. I am just in my infancy with hyperlinks and macros and do not feel confident enough to start there as I do not know where to begin. Keen to learn and would love to figure out and play around within a program; learn best this way.
I run excel 2010 on a windows 8.1 machine. In excel I have email address that I need to mail from however when I click the hyperlink it automatically directs me to Hotmail. Is there a way to change that to automatically load Outlook instead?
i have the following code in my Excel worksheet. This code successfully send out email to our customers when if the criteria is true. I have tested this at home and it works perfectly as I use Outlook at home. However when I take this code to work I couldn't get this working as at work we use Outlook 365 and we use web Outlook, OWA.
Is there a setting I can specify to use OWA as I don't think I am allowed to install outlook at work.
I am trying to export the emails from outlook to an excel by the normal method. Here the body of the method is not exporting properly in to my excel. Is there any macro or a vb code to export the body of the messages to excel.
Trying to find a way to close outlook from excel workbook close. If outlook is open then delay excel workbook 45 seconds from closing.If outlook is not open then close excel normal. Here is what I've tried with no luck
Bank a1a $50,000.00 1008:00 PM Bank b1b $51,000.00 2009:00 PM Bank c1c $50,000.00 30010:00 PM Bank a1a $53,000.00 40011:00 PM Bank e1e $54,000.00 50012:00 AM Bank f1f $55,000.00 6001:00 AM
I use a report daily at work that is filled in during the day; I'm wanting to minimize some of the work to be more efficient. VBA to search a particular folder in outlook (ex: inbox - cust info ) and search within those emails to verify 3 items match (Cust ID / $ / items) before extracting data and putting it into column F for that item. For instance, bank a and bank c have $ values that match, but the other criteria dont - Cust ID and items. Also is there a way to attach a copy of that email to the spreadsheet, say in column G, it doesnt have to populate in the spreadsheet, but maybe the attachment will open even 6 months down the road? I am worried that a link will eventually get lost if an email is erased.
Due to one of our business requirement I have to fetch so many users Alias name through Outlook.
I am following below steps to achieve that Open new mail, giving user's name in to list, press ctrl+k, then right click properties. In properties I use to find alias name of user. I want to automate this process through excel sheet. I want to provide names in one column as Input to excel sheet and want to fetch alias name in next column of excel sheet.
I've used some code from another thread on the forum to create an Outlook Appointment and email this as an attached .ics file from an Excel spreadsheet. Everything seems to work fine. The email is sent and the attachment when double clicked opens in Outlook with all the correct information, but when the recipient clicks 'Save and Close' it doesn't save in their calaendar....
I am trying to merge my boss's contacts. He has one enormous set of contacts in GMail (5000+) and one enormous set of contacts in Excel (5000+) I've exported both sets into excel but how to I merge them?
The headers for each set are in a slightly different order (Home Address, Office Address, Email ...vs.....Office Address, Home Address, Email) Do I need to make sure the order of the headers match in both the GMail Sheet and the Outlook Sheet and is there a faster way to do that than just rearranging them manually?
My second question is how do I merge them? There are a lot of over-laps of people but some info might be different in Outlook than in GMail. For example I might have John Smith as a contact in both Gmail and Outlook but two different email addresses for him under the header "Email 1" how do I make sure one email doesn't eliminate the other? I just want to merge both sets of contact info for each person into one super contact.