Computer Support Forum

Additional code to Excel macro - export to Outlook

Question: Additional code to Excel macro - export to Outlook

Hi there!

So I've found the code below here: http://www.google.com/support/forum/p/Calendar/thread?tid=5e4909ca64cffe90&hl=en - Full credit goes to user 'tpuiatti'

Code:
Sub Generate_ICS()
Dim rng1 As Range, X, i As Long, v As Long
Dim objFSO, objFile
Dim FilePath As String
FilePath = "G:\Service.ics"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(FilePath)
Set rng1 = Range([a5], Cells(Rows.Count, "H").End(xlUp))
X = rng1

objFile.write "BEGIN:VCALENDAR" & vbCrLf
For i = 1 To UBound(X, 1)
objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
Next i
objFile.write "END:VCALENDAR"
End Sub

This code takes the info from 2 columns in Excel: A which is the event title and B which is the event date. Then it export these informations to an .ics file which will add the event dates to the Oulook Calendar.

The macro and the .ics work perfectly, however i wan't to make a few changes/additions to fit my needs. Additionally I wan't this macro to:

- Pick up dates from column 'F' instead of 'B'. (I've tried to just change B to F, but that didn't work)
- Make the events all day events, so that the starting time is not showed up in the calendar, but only the title/name.
- Put in a reminder 1 week before.
- Place all the events in a category.
- Include additional information (another column) with descriptions for every event.. And also add a 4th column with location info.
I know nothing about Excel macros and/or VBA, but the solution might be simple.

Thanks in advance!

Best regards
Jeppe C.

Relevance 100%
Preferred Solution: Additional code to Excel macro - export to Outlook

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Additional code to Excel macro - export to Outlook

7 more replies
Relevance 82.94%

Hello Everyone
I'm a pro atlete (quite iliterate in Biz Apps) that based on previous post (from Zack Baresse and computerman29642) has unsuccessfully tried to set up a code to merge my next year training calendar (xls format) into outlook. I herewith included the code I'm using and the test xls file I'd love to merge. Any help would be really appreciated
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = Startdate
' Row 2 = Finishdate
' Row 3 = Subject
' Row 4 = Location
' Row 5 = Body
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt

.StartDate = arrAppt(i, 1) + arrAppt(i, 2)
.Finishdate = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = arrAppt(i, 6)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.AllDayEvent = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub... Read more

More replies
Relevance 95.53%

Good Morning,

I have very limited knowledge when it comes to Macros and VBA.

What I need is one of two possible scenarios.

1. Information from a Training Assignment PivotTable exported to a shared calendar on Outlook.
2. Information from a Training Calendar bulit as a Macro in Excel exported to a shared calendar on Outlook and the ability to update either calendar if new information becomes available.

Don't know which is best using code via VBA or using the Export/Import functions.

Any help is greatly appreciated
 

More replies
Relevance 80.36%

I need to export the contents of one and the same cell to 100 different text files.

For example... I need the contents of Sheet1 Cell A1 to be exported to new text files. The names for each text file is on Sheet2 Cell A1 - A100.

Does anyone have a code example for this? I have search and found some similar but i couldnt figure out the code to modify it.

Thank you in advanced.

asm
 

Answer:Export Excel Cell with Macro

Hi asm,
and will the exported file be a new one or can it be an existing text file?
If the file is already presnt then what? Delete the file or append the new data to it?
Which version of EXCEL are you using?
Macro's a problem ?
 

1 more replies
Relevance 79.13%

Is there a way of exporting a query directly query directly into Mircrosoft excel? I have a query that I am trying to distribute to people in excel format but i would like a button that triggers the export of the query to excel so i can send it out via email.
 

Answer:Export Query Directly to excel using a macro

Yes, in the Access Visual Basic Editor's Help (not Access's ordinary help) look up Transfer Spreadsheet and Transfer Sreadsheet Action.
 

1 more replies
Relevance 78.31%

I'd like to create a macro which creates a new MS Word document in the same directory as the excel book which copies specific data from an excel worksheet into the new document. The items contained within the cells should appear as a bulleted list on the word document, not a table.

Is this possible?
 

Answer:Macro to Export Excel Data to New Word Documet

I'm not a Word macro person at all..But I'm thinking (quick and dirty) you could do this with mail merge. Don't use the address part just 'insert merged' fields...Your 'cells' would have to be in the same row in excel.

There are also excel to word conversion programs(google). I have never used one. If your data was in Access a 'Report' would suit your needs. Also a script that reads the excel file and writes to file and saves as .doc might do this also.

Going to word 'for the sake of being in Word' seems counter productive but management wants what management wants.
 

1 more replies
Relevance 74.21%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 73.8%

I'm working on a spreadsheet in Excel with three workbooks. The first two contain forms for the user to input data and the third displays the results and allows the results to be printed. This is working as I wish at the moment, but I would like to extend it's functionality by exporting the data from the third workbook into an Access table.

I have googled this extensively but haven't been able to find working code that will satisfy my situation, or even anything that I can adapt. This is the first project that I've attempted in Excel, and I realise that in trying to export data to Access I'm in slightly over my head.

I have four cells that I want to export to Access: text, an integer, the date, and a time in minutes and seconds. I have therefore created an access database with the four appropriate fields, and also a fifth for a unique ID (which I understand access will handle.)

I'm looking for some VBA code that can be run by pressing a button and will export the four cells into Access. At the moment the cells are between J1 and J4 in one Workbook.

Does anyone know of a tutorial that I can follow to achieve this? Or of any code that I can make basic edits to?

Any help will be greatly appreciated because I know that I am asking for quite a lot, and appreciate that I am slightly out of my depth. I've come really far in this project though, though advice on HF and Googling, and would be pleased to put the finishing touches on this project.... Read more

Answer:Excel VBA Code to export to access table

Assuming that you have the desired schema already setup in the Access file, there's a couple ways I see this can be achieved:
1 - Write some VBA within the Access file to connect to the Excel sheet, loop through the sheet/rows/columns however you need, and load rows into the Access table of your choice.
2 - Do the same as #1, but the worker logic is in the Excel file's VBA.
3 - If you're comfortable with writing about a dozen lines of code in C# or Java or some other more robust language. Then you can use ODBC connectors to execute some (limited) SQL against the Excel file, iterate however needed through the resultset, and send INSERT (or UPDATE) statements to the Access database.

Personally, I would look at #3 or #1.

I would also question whether Access is an appropriate datastore for your needs; something like SQL Server Express would be free and can avoid a few potential issues with Access data storage. (e.g. growth limitations, odd-ball datatypes like OLE, etc.)

As for writing code... You can take a stab at VBA or another language. There's plenty of people that can assist in this forum on a case-by-case basis, though you usually have to show that you're making efforts yourself (code snippets, progress updates, etc.). Alternatively, get in touch with a contracting agency and ask them for some temporary help. You can budget time for not only completing the work, but also for knowledge transfer and training.
*Edit*
Just re-read and noticed this:... Read more

4 more replies
Relevance 73.39%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 70.93%

Sub GetData()
Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Raw Data").Range("DD").Value = "YesData" Then
Worksheets("DD").Range("A1:Y100").Value = Worksheets("Raw Data").Range("A1:Y34").Value
End If

In the above code I am trying to populate another sheet "DD" with specific information from "Raw Data". In the If statement I want the data populated to only retrieve data rows from "RawData" that contains the word "YesData" in the D column.

Please help.
 

More replies
Relevance 70.93%
Question: EXCEL macro code

I am trying to macro code a button "UPDATE" in excel where i want to it to function as if column M5:M50 has a "Y" in it then copy the entire row and paste it in sheet 2 and delete it from the old. I am new to coding and have no idea how to do thismy rows start with info at row 5 there is no end to how many i may have but i will say it probably will never hit 50 Please Helpthe sheet i want it to paste is called SCG transfers

Answer:EXCEL macro code

You didn't give us the name of the sheet that contains the original data, so I used Source Sheet in this code. Before you run it, you'll need to change every occurrence of Source Sheet to the name of your sheet, or change the name of your sheet to Source Sheet.I strongly suggest that you try this code in a backup copy of your bookwork since it deletes rows and those deletions can not be undone.Sub MoveY()
'Determine how many rows of data in Source Sheet
lastRw = Sheets("Source Sheet").Range("M" & Rows.Count).End(xlUp).Row
'Loop through rows
For nxtY = 5 To lastRw
'If there is a Y in Column M, determine the next
'empty row in SCG transfers and copy the Row
If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
nxtRw = Sheets("SCG transfers").Range("M" & Rows.Count).End(xlUp).Row + 1
Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Copy _
Destination:=Sheets("SCG transfers").Range("A" & nxtRw)
End If
Next
'Loop through Source Sheet rows in reverse order so that they can be deleted
For nxtY = lastRw To 5 Step -1
'If there is a Y in Column M, delete the Row
If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Delete
End If
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 70.93%

Hi,
I am using excel 2007. I have some cells in sheet 1 (made to look like a form) and i have created a macro to take the data from these cells and put them in a row on sheet 2. However, what i cannot figure out it what command i need to insert (and where) to have the data go to the next empty row.
This is what i have recorded on the macro:
Range("D9").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D19").Select
Application.CutCopyMode = False
Selection.Copy
Sh... Read more

Answer:help with Macro code in excel

Copy selected cells to next sheet

This will work for you

Code:
Sub move_info()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim Rng As Range, c As Range, x, Rws As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Rws = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = ws1.Range("D9,D11,D13,D15,D17,D19,D21,D23")
x = 1

For Each c In Rng.Cells

ws2.Cells(Rows.Count, x).End(xlUp).Offset(1, 0) = c
x = x + 1

Next c

End Sub

Range Selection Codes
 

3 more replies
Relevance 70.52%

Hello,

I have two problems:

1.
I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

2.
I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

Any help is greatly appreciated.
 

Answer:Solved: ACCESS: Show second latest data and export data to excel from macro

14 more replies
Relevance 70.52%

Hello all,

I have been presented with a project that involves Forms that I am not able
to resolve.

Basically I have been asked to create a document template where other team
members can fill out a form that asks a series of questions, then to have
only the completed form results export to a new file.

I am aware that Word can export the form results to a text file, but when I
do this all of the questions that were on the original form are exported to
the new file.

What we are looking to achieve in the end:
If the end user is faced with 20 questions, but only 12 of the questions
pertain to the end users project, only the 12 completed form fields will
export to a new file.

I know that this should be possible with using a macro button to export the data, but that is where I need the help.

I tried searching for this exact situation, but I did not find any matches, hence the new thread.

Thank you in advance,

Bill
 

Answer:Macro help needed to export Word form results to a new file... Word/Excel

11 more replies
Relevance 70.52%

Hi guys,

I've stumbled across another problem .

I've managed to locate a decent code for extracting mail from outlook and putting it into Excel. everything is fine but i would like to know if anyone could write me a little snippet to make the macro get certain bits from the e-mail.

For example i have an e-mail and i would like the macro to search for "Subject:" and because the text after the subject is on the same line i would like it gather that too
 

More replies
Relevance 70.52%

Can anybody help me with some code?

I need to be able to export a non-static range of cells from and Excel file and have this range populate the body of an Outlook Email.

I can get VBA to open the email and automatically populate the distribution list, but I am having trouble with the body of the message.

Thanks
 

Answer:Need VBA help to export from Excel to Outlook

Post a sample workbook and I'll help you. I also need more details on what exactly will be contained in the body of the email? Is the body of the email going to contain a list? Will the email contain some standard text combined with variable values from the workbook? More details please.

Regards,
Rollin
 

1 more replies
Relevance 70.11%

I'm trying to code a macro with (among other things) a section using countif(). For now let's just assume that the range is fixed, say C3:C43. The criteria needs to reference 2 other cells. I want the countif to equal

(the # of values <= the # in cell(31)) - (the # of values <= the # in cell(30)), both in a different column.

I can select the range but I don't know excel's code for using countif() in a macro. Here's what I tried so far:

Range("C3:C43").Select
a = Selection.CountIf("<=m-<=p")

where m=# in cell(31) and p=# in cell(30)
I get an Object doesn't support this method error.

How can I get there? TIA
 

Answer:excel vb macro code for countif()

7 more replies
Relevance 70.11%

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
defTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals

Thanks!!!
 

Answer:Solved: Excel 03 Macro/Code

8 more replies
Relevance 70.11%

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

Answer:Solved: Excel-macro code help

15 more replies
Relevance 70.11%

I've actually written a line of code that is too long to fit in the macro window. How can I continue the code on another line in the programming window? I've tried using &'s but it doesn't work. Thanks.
 

Answer:Excel Macro - Code Continuation

6 more replies
Relevance 70.11%

I am trying to create an excel (2007) macro (in windows XP) which will compare a list of data on 1 worksheet with that on another worksheet within the same workbook and then have an out put in the first spread sheet.
Example.
ID Number​​​​​​first name​​​​​​last name ​​​​​​job type code​​​​​​qualified​​​​​​
1234​​​​​Bill​​​​​Smith​​​​​BLK​​​​​
Compare
5678​​​​​John​​​​​Citizen​​​​​REV​​​​​

1000​​​​​Paul​​​​​Person​​​​​MIF​​​​​
Clear

(on sheet 1) columns 1,2,3 & 4 would be prepopulated (via a copy and paste method), an action button "Compare" would be used, this would then compare the data in the "Job type code" fields with the "qualifications" listings from a range that would be listed on worksheet 2 of the same workbook. Example see below

BLK Qualifications
Certificate of economics
Certificate of accounting
REV Qualifications
Certificate of leisure
Certificate of Hope



MIF Qualifications
Certificate of S... Read more

Answer:help creating code for an excel macro

You say what you are trying to do, but...
What is the question?
 

3 more replies
Relevance 70.11%

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.
 

Answer:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)

Rollin
 

2 more replies
Relevance 69.7%

I am trying to export the body of text email that customers fill out for a form on the web. It comes in the form of:In-Home Energy Audit Request From:John Customer11 Coolidge PlaceDurham NC 27705(919) [email protected] Time for an Appointment: MorningApproximate Square Footage of Home: 3000Year Home was Built: 1999Type of Home: SidingType of Foundation: crawl spaceType of Fireplace: Natural GasI have the following natural gas appliances in my home:Water HeaterFurnace I would like to export each piece of data after the colon to an excel column. I used VBA but it puts all my data in one column for the body.

Answer:Export Outlook body to excel

Hello,I have exactly the same requirement as you -- same problem with web form data when I extract to Excel. Please let me know if you have found a solution. Thanks

2 more replies
Relevance 69.7%

I would like to export the my task list to excel so I can create a report. Can this be done and if so how?

Answer:can you export outlook tasks to excel

Click here.Posting Tip: Before posting Data or VBA Code, read this How-To.

3 more replies
Relevance 69.7%

I need to create a database out of daily emails dropping in a specific folder.

1> Emails seggregated into folders upon arrival with the help of RULES,

2> Upon arrival, same emails are Exported to Excel file

3> Excel file on Auto Save & Auto Update mode

4> all exported data is formatted as Database in Excel

5> Final view of Excel database on Internet Explorer Web View having a search box to run specific DATA search

Can anyone please help me out with this project with detailed guides.
 

More replies
Relevance 69.7%

Hi Guys,
I have following queries regarding Outlook 2007 contact management .I am trying to export excel format using the following code to outlook 2007,contacts list, but its not working ..can someone assist me in sorting this out ..i am attaching the format i need to export and here is the code i am using

Code:
Dim appOutlook As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objContactFolder As Outlook.MAPIFolder
Dim objContacts As Outlook.ContactItem
'Dim myDistList As Outlook.DistListItem
Sub ContList()

Set appOutlook = GetObject(, "Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
Set objContactFolder = objNameSpace.GetDefaultFolder(olFolderContacts)
Set myMailItem = appOutlook.CreateItem(olMailItem)
Set myRecipients = myMailItem.Recipients
'Set myDistList = appOutlook.CreateItem(olDistributionListItem)
For i = 3 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set objContacts = objContactFolder.Items.Add(olContactItem)
With objContacts
'.Secs = Range("B" & i).Value '.CompanyName = Range("B" & i).Value
.BusinessName = Range("C" & i).Value '.LastName = Range("C" & i).Value
.ContactName = Range("D" & i).Value '.FirstName = Range("D" & i).Value
.Custemail = Range("E" & i).Value '.BusinessAddress = Range("E" & i).Value
.Traderemail = Range("F&qu... Read more

More replies
Relevance 69.7%

I have a workbook that has 30 worksheets inside it. I need to be able to save each worksheet as an excel file with the tab name being the file name. I have been able to save them to PDF files but I can't seem to get it to work for the excel side. Thanks

Answer:Macro Code, Save as Excel Document

I image it'd be something like this:Dim s As Variant
For Each s In ActiveWorkbook.Sheets
s.SaveAs s.Name, ActiveWorkbook.FileFormat
Next 'sHow To Ask Questions The Smart Way

3 more replies
Relevance 69.7%

Hey,
Can someone tell me what I need to do to have a macro in excel, open another file?
For example, I need the following file opened:
c:\FolderA\FolderB\FileC.xls
How do I do this, and also, at the end of the macro, I want that file closed. Please help!
Thanks,
 

Answer:VBA Code to open file in Excel Macro

Give one of these lines of code a shot...
Code:

Workbooks.Open("Book2.xls")

or
Code:

Workbooks.Open("C:\MYFILES\BOOK2.XLS")

What are you going to do with the spreadsheet once opened?
 

2 more replies
Relevance 69.7%

Hello,
I have the following bit of code as part of an Excel macro:

'In G1, enter formula to apply 6 character French code for AUT vehicles
ActiveWindow.ScrollColumn = 1
Application.Goto Reference:="R1C7"
Range("G1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(FIND(""AUTFORD"",RC[-6])),""FORD00"",IF(ISNUMBER(FIND(""AUTVAUX"",RC[-6])),""VAUXHA"",IF(ISNUMBER(FIND(""VNVF1"",RC[-6])),""NISSAN"",IF(ISNUMBER(FIND(""VF1"",RC[-6])),""RENAUL"",IF(ISBLANK(RC[-6]),"""",""MISC00"")))))"
Range("G1").Select

I'd like to be able to add more IFs to it, but there are already 7 conditions in there, which I believe is the max number of nested IFs allowed.
Specifically, at present, I want to add:

IF(ISNUMBER(FIND(""AUTNIS"",RC[-6])),""NISSAN""

But I may need to add more in the future.

Can someone help me write this in a better way? I'm no VB genius...
 

Answer:Excel 2003 macro - rewrite a little bit of VB Code

16 more replies
Relevance 69.7%

I have a massive excel spreadsheet containing 6 worksheets and need to hide certain rows throughout the workbook if certain cells in 'sheet1' are left blank. I also need excel to automatically hide the rows b/c other people will be using the workbook. I don't want them to have to hit "run macro" each time they modify the cells in 'sheet1'. Also, ALL the rows in worksheets 2 thru 6 contain formulas if that matters. Sheet1 does NOT contain any formulas.If 'Sheet1' B22 is left blank, then I need excel to hide the following rows:'Sheet2' row 11'Sheet4' row 10, 26,27,28,57,68,78,90,107,108,109'Sheet5' row 7'Sheet6' row 6,7,8,9,50,51,52,53If 'Sheet1' B23 is left blank, then I need excel to hide the following rows:'Sheet2' row 12'Sheet4' row 11, 29,30,31,58,69,79,91,110,111,112'Sheet5' row 7'Sheet6' row 6,7,8,9,50,51,52,53etc.I'm trying to make slight adjustments to the following macro to accomadate my worksheet but it's not working at all:Private Sub Worksheet_Change(ByVal Target As Range)Dim MyCells As Range'Which cells do you want to monitor for changes?Set MyCells = Range("A1:A42")'Check to see if the changed cell is one we care aboutIf Intersect(Target, MyCells) Is Nothing Then Exit SubIf Target.Count > 1 Then Exit Sub 'Don't do anything is multiple cell changes'Turn off system settingsApplication.EnableEvents = FalseApplication.ScreenUpdating = FalseWorksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""Application.EnableEvents = TrueApplication.ScreenUpdating ... Read more

Answer:Excel Hide Rows If Macro Code

First, a posting tip for this forum:Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.As for your question, what is this line supposed to be doing?Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""That is not an instruction that VBA will recognize and I don't see how it relates to your requirements. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 69.7%

.In a bridge tournament each team plays 8 rounds (eight other couples).

.the spread sheet uses column A to list all teams (from 1 to x). There could be

as many as 25 teams.

.After each round each teams score in entered in that rounds column .

For example after the first round each teams score is entered in column C







A B C D E F G H I J K



________________ROUND_____________



Team # Names 1 2 3 4 5 6 7 8 Total Score

1 joe-sandy 1650 1650

2 al-sally 980 980

3 tom-ann 1100 1100





When all scores are entered the spread sheet (score sheet) is sorted in

descending order by column K (total score of each team) and posted on a

Bulletin board. This shows every team their relative position (1 place, 2 place, etc.

to last place).

.This means that there will be 8 series of entering scores onto a master spread

sheet and 8 sorts showing every teams position after each round. (rounds 1,2,3,4,5,6,7 & 8(final))



The problem is that if the scoreke... Read more

More replies
Relevance 69.7%

Hi all, hope someone can help.

I have a list of contracts, each run for 3 years exactly.
One starting 12/04/2007 ends: 11/04/2010.

I have the following code:

Code:

Sub Add3Years()
If IsDate(Range("B2")) Then
ActiveCell.FormulaR1C1 = DateAdd("yyyy", 3, Range("B2"))
ActiveCell.FormulaR1C1 = DateAdd("d", -1, ActiveCell.FormulaR1C1)
End If
End Sub

However, as you can see the initial range is as the specific cell B2. What I need is for the macro to see that I am on row 20, goto Column $B on row 20 and use that value instead of
Range("B2")Click to expand...

.

I've tried offsetting, but that doesn't really work, and I've seen
varRowNumber=Activecell.Row will return the row numberClick to expand...

If someone could help join the dots, that would be appreciated.
Thanks.
 

Answer:excel vba/macro: relative reference in code.

7 more replies
Relevance 69.7%

Is there a code to where I can have a macro run every 5 seconds? If so, where would I place the code?
 

Answer:Solved: Excel Macro/Run Code Every 5 Seconds

Hi
here's how Ive done it. In this example C1will contain a single digit representing the # seconds so that I can control it from the sheet rather than editing the macro. If you just want to hard code the 5 seconds you can set Freq="00:00:05" or get rid of that line and edit the next one to

TimeToRun = Now + TimeValue("00:00:05")

There are 2 macros involved ....

the first sets the time period to run the routine you want and then "calls" that macro
the second is the routine you want to run every 5 seconds. The final line in this macro is to call the first macro to start the timer again
Code:

[B]Sub ScheduleCopyPriceOver[/B]()
Freq = "00:00:0" & Range("C1")
TimeToRun = Now + TimeValue(Freq)
Application.OnTime TimeToRun, "[B][COLOR=red]CopyPriceOver[/COLOR][/B]"
[B]End Sub[/B]
'
[B]Sub CopyPriceOver[/B]()
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Do what you need to do
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Call [B][COLOR=red]ScheduleCopyPriceOver[/COLOR][/B]
[B]End Sub[/B]
Finally, if you want to start the timer when the the file is opened you can add another macro like this
Code:
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
All of these macros can be in a normal module.
 

2 more replies
Relevance 69.7%

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

Range("C920").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

Answer:Solved: excel vb macro code for chart

10 more replies
Relevance 68.88%

I want to export all my contacts in Outlook 2010 to Excel. Suggestions?

Why: I am fairly sure my outlook file is corrupt. I cannot see my contacts in category view as I did when I had the same program in WinXP (I upgraded to Win7.) After 8 months, the first category appeared in the Current View box, but no others. I can see the categories in list view. I thought I would find some errors when I export them to a file I can edit.

Alternatively, is there any way to view the main Outlook file, (.pst?) in an external editor?

Answer:How to export Outlook 2010 contacts to Excel?

Sorry folks, found the export under "open."

2 more replies
Relevance 68.88%

Help please. Trying to import names and e mail addresses from am Excel spread sheet to my Outlook Contacts. I know the process File<Import <Excel find file etc however when I get to the stage I get a warning ' Cannot import Excel Translator not installed. Would you like to install it now?. Select Yes system searches for the files and then informs me they are not available.

I have XP Home and MS Office Pro 2003 with full package. I have tried checking Installed components from Add remove Programmes and gone round innumerable circles.

Searches on the MS sites have not revealed any answers either.

Is there an easy fix for this can anyone help please. Hope I am posting in the right area - could not see anything specific about Office or Excel Translators.
Thanks
David
 

More replies
Relevance 68.88%

Sirs,I have a workbook in excel with 3 sheets. Sheet 1 ("COLUMNAR")ID DATE PAGE NO OR NO NAME OF PAYEE AMOUNT SECTION PARTICULARS 1 1/5/11 PAGE 1 0000701 JOEY BANTOG 26.00 DRY GOODS Sheet 2 ("SAMPLE") Page 1 Serial No. Amount(From - To) PayEE Acct. Code Description A B C D EI need to automatically transfer Serial No, Payee and amount to Sheet Named "Sample" which is the report form leaving Acct Code blank however using the Page No as the ref cell Sirs please help me my job is at stake with this report. Thanks

Answer:Macro Code to copy range of data in excel

Please repost your data after reading the How To referenced in the following line:Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 68.88%

Hey everyone,
So I used this code to copy a range of cells from multiple workbooks and paste it into one workbook. It used to work fine but now it gives me the 400 error. I've been adding files to the directory that it pull the files from and it was working fine, but all of a sudden it's causing this error. Can someone please help me out?

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String
Dim wblist() As String
Dim i As Integer
Dim wbname As String
Dim j As Integer

i = 0
j = 0
FolderName = "C:\Documents and Settings\s.k\Desktop\CRs\LOG"
wbname = Dir(FolderName & "\" & "*.xls")

Application.ScreenUpdating = False

Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir
Set ycell = Range(Cells(i + 3, 2), Cells(i + 2, 28))
Set xcell = Range(Cells(2, 3), Cells(2, 28))
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wblist(i) & "]" _
& sheetname & "'!" & xcell.Address
Loop

Do While j < 100
Cells(j + 3, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],4)"
'ActiveCell.FormulaR1C1 = "=LEFT[RC[6],4]"

Cells(3 + j, 1) = Val(Cells(3 + j, 1))
Cells(3 + j, 2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CR Status.xlsx]Sheet1'!R3C1:R189C3,3,FALSE)"

If Cells(3 + j, 1).Value = 0 Then
Cel... Read more

Answer:Excel VBA Macro Problem - Code used to work, now 400 error

7 more replies
Relevance 68.88%

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim
 

Answer:Solved: Need Excel Macro Code to Autofill Column

12 more replies
Relevance 68.88%

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

Answer:Solved: Excel: Copy/Paste Macro Code Help

9 more replies
Relevance 68.47%

I have gotten as far in exporting an excel file to outlook contacts to a screen that asks for Select Destination Folder. The screen is blank. Appreciate any help.
 

More replies
Relevance 68.06%

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 

Answer:Excel 2003 macro code converting comma delimiters

9 more replies
Relevance 68.06%

Hi,I need an Excel Macro code for split the Cell contents and copy the split data into 2 or more number of lines.My Requirement. Sheet1:Column A2 : 1 Column B2 : Omega,M120,001Column C2 : External hard drive 2.0Column D2 : EachColumn E2 : New,New,NewColumn F2 : 250GB,500GB,1TBColumn G2 : ABC Tech, IT info, Tech ShopColumn H2 : 3yrs warrantyColumn I2 : 1QtyCondition verification : i have this raw data. here first i need to split the Column B2 into Three Column and then have to verify how many item in Column E2, that much i split into new row and same thing for Column F2 & G2 and remaining data should be copy the same.i need output like thisSheet2 :Column A2 : 1Column B2 : 1Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 250GBColumn J2 : ABC TechColumn K2 : 3yrs warrantyColumn L2 : 1QtyColumn A2 : 1Column B2 : 2Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 500GBColumn J2 : IT Info Column K2 : 3yrs warrantyColumn L2 : 1QtyColumn A2 : 1Column B2 : 3Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 1TBColumn J2 : Tech Shop Column K2 : 3yrs warrantyColumn L2 : 1QtyPlease help me anyone..Thanks & RegardsLJ

Answer:i need excel macro code for spliting cell data & copy

Let's start with getting the terminology right.You used the terms Column A2, Column B2, etc.A2, B2, etc. are Cell references, not Column references. A, B, etc. are Columns, 2,3, etc. are Rows, A2, B2, etc. are Cells.As for your question, the output in your example doesn't make sense.You have requested that all of your output go into Sheet2!A2:L2. In other words, you have requested that all 3 sets of data end up in the same row (2) in Columns A through L.That can't happen. They have to be in different rows or the last entry will overwrite the earlier ones.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 66.42%

Hey guys, I had some great assistance from computerman29642 earlier on this macro. It works perfectly on my own calendar; however, I need to target a specific shared calendar. Hopefully it could be user imput by a msg box. I am struggling to find information on how to do this.

The first thread was this
http://forums.techguy.org/business-applications/835748-solved-excel-macro-outlook-calendar.html

So, as for now the example works. But before it writes to the calendar I want a msg box pops up asking for the desired calendar(Sometimes more than one word), and then target that calendar and write the data. A simple little box that confirms or denies progress (I am pretty sure I can figure that one out) Any suggestions or directions where I can learn more fire away please. Ill attach a copy of my test again.
 

Answer:Excel to Outlook Macro

16 more replies
Relevance 65.6%

Hi,

I am an extremely basic user who has managed to get a basic macro together through multiple web searches and am now stuck...

I have dates entered into the following columns in excel: 14,15,16,26,27,28

I have managed to make the macro create appointments for 14,15,16 but not the rest. Also, every time i run the macro it creates a new appointment regardless if one already exists. I saw on this forum about adding a word to another column which the macro searches for and if found doesnt create a new appointment - or something to that extent. Here is what i have:

Sub POATEST()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

' Start at row 7
r = 7

Do Until Trim(Cells(r, 1).Value) = ""
For Each cell In Range(Cells(r, 14), Cells(r, 16))
If cell.Value <> "" Or 0 Then

' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)

' Set the appointment properties
myApt.Subject = Cells(r, 1).Value & " " & "Update Due"
myApt.Start = cell.Value
myApt.categories = "Yellow Category"
myApt.ReminderSet = True
myApt.Body = "blah blah blah"
myApt.Save
End If
Next cell
r = r + 1
Loop
End Sub

Please can someone assist by:
1. making it apply for all the columns originally mentioned
2. making it search if an appointment for that item (ie that row) exists - if not, create appt, if so, do not create appt and insert "Yes" in column 29

Thank... Read more

Answer:Excel Macro - Add Appointment To Outlook

HI,

Check this code you may be able to modify it.
Have not used it in a long time but....

Code:

Sub AddAppointmentsToCalendar()
Dim OLF As Outlook.MAPIFolder, objItem As Outlook.AppointmentItem
Dim i As Long, lngItemCount As Long, r As Long
On Error Resume Next
Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
On Error GoTo 0
If OLF Is Nothing Then Exit Sub

Application.StatusBar = "Adding appointments to Outlook..."
With wsSheet1
.Activate
r = Range("NewAppointments").Row + 1
Do While Len(Range("A" & r).Formula) > 0
On Error Resume Next
Set objItem = OLF.Items.Add(olAppointmentItem)
On Error GoTo 0
If Not objItem Is Nothing Then
With objItem
.Start = Range("A" & r).Value
.End = Range("B" & r).Value
.Subject = Range("C" & r).Value
.Body = Range("D" & r).Value
.Categories = Range("E" & r).Value
.ReminderSet = False
.Save
End With
Set objItem = Nothing
End If
r = r + 1
Loop
End With
Application.StatusBar = False
Sheets("Appointments").Select
Range(&... Read more

1 more replies
Relevance 64.78%

Moved from Software Development
Hi, We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in to emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed so they could be updated by all. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I have updated an excel spreadsheet containing all the information and I would like to create a macro for all the team to periodicaly import this data in to Outlook Contacts and possibly create distribution lists from these. I trust this makes sense I am using Outlook 2003.
Thanks
 

More replies
Relevance 64.78%

Hi

This is a tuffy........

I want a macro so that when i type a message into a cell it will put it into outlook as a reminder, or even a simple macro button that opens outlook in the scheduler.

Any ideas?

Thanks
 

Answer:Excel macro links to Outlook Reminder

Try this link.
 

1 more replies
Relevance 64.78%

I would like to ask for an Excel macro which can send an automate email on a certain date every Month.

The dates would be as follows:

1st Monday: Email should be sent to [email protected] automatically
2nd Wednesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically

Subject line: ABC123D5679_Data transfer expected today

Details of the body of the text message:

"Dear All,
This is to remind you that we are expecting the data transfer to be posted in Novartis system today . This is just an advanced intimation.

Kindly let us know if you foresee a delay in the data transfer.

Kind regards,
Rohit"
Many thanks in advance for your help guys. This means a lot.
 

More replies
Relevance 64.78%

Hey guys, I am quite new to creating macros. I do have some primitive experience with code. My goal is to take 3 or 4 colums of information and put them into Outlook Calendar.

A- Discription B- Start -C Finish

So, in the Calendar in outlook it would show a discription and connect the dates in B and C to the days in the Calendar. I don't know if it can be done. At my current state I can't figure out how to get even one to move over. Any suggestions on where to start??

I am running Outlook and Excel 2003 all updated. On Windows XP Pro Service Pack 2.
 

Answer:Solved: Excel Macro to Outlook Calendar

16 more replies
Relevance 64.78%

I need a macro that will 1) create a new outlook folder, then 2) create new Outlook distribution lists from data in an Excel sheet.

The Excel sheet has columns:
A - ID
B - DEPARTMENT_DESC
C - STUDENT_LEVEL_DESC
D - MAJOR_DESC
E - PROGRAM_DESC
F - EMAIL_ADDRESS
G - LAST_NAME
H - FIRST_NAME

The first entry begins in row 2 with row 1 being the column title.

I need a new distribution list to be created for each department (column B) and each student level (column C). (Column B will only ever be one of 6 values, column C will only ever be one of 2 values. Therefore, the macro should create 8 distribution lists.)

I have looked at threads similar to this question but I can't seem to get anything working.
 

Answer:Macro from Excel to New Outlook Distribution List

I saw something you might could work with here: http://www.helenfeddema.com/CodeSamples.htm
scroll down for sample 61. I haven't used it but marked it one day. I'm assuming your wanting to make local distb list? Not in AD.
 

1 more replies
Relevance 64.78%

Hi all,

I'm looking to create an Excel document and I need to put cells with dates in that are linked to outlook calendar reminders.

I.e I need a macro that when I click the link it sets the reminder on outlook for the date allocated in the cell.

could anyone please help me with this as i have never created a macro and would greatly appreciate any help you could give me.

Thanks

More replies
Relevance 63.96%

Hi there,

I have to prepare several daily emails which are prepared using a range of data (for example B3122) from Excel 2003 and then have to be sent using Outlook 2003. Unfortunately, due to various management requirements I am unable to merely attach the Excel file as an attachment or paste the Excel data into the email as a straight paste - I have to paste as Unformatted text and then manually change various lines of the email to Bold and Underlined text.
I am trying to write a macro that will do this automatically but am unable to get the Excel data pasted into Outlook (as unformatted text or otherwise). I am assuming that for the font changes to bold I will have to use a Find function in the macro.

The code that I have got so far is as follows:

Range("B3122").Select
selection.Copy

Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Subject = Range("B3")
olMail.Display

I am new to VBA and so I may be missing a really obvious solution but after trawling the net I have been unable to find an answer.

Many thanks to anyone that can help and make my work a lot less tedious!
 

More replies
Relevance 63.96%

Hi, I have a macro in excel that automatically updates my outlook calender - I use it to keep track of actions/deadlines/etc...

The macro runs beautifully except... whenever I run it it creates duplicates of appointments it has previously made in my outlook calendar. Is there any thing that I can add to the macro below to ensure it doesn't duplicate?

Really appreciate any help on this one. Cheers.
Sub outlookexport()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i
Set olApt = Nothing
Set olApp =... Read more

More replies
Relevance 63.96%

Hi, I am looking for a macro to import contacts from excel. We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I would like to create the macro for all the team so that they can periodicaly update their contacts. I trust this makes sense
Thanks
Moved to Business Applications.
 

More replies
Relevance 63.55%

Ok, just a bit of advice needed on this one for the time being...
I need a macro that will:

- Save all (Excel) attachments from the currently open email message (or a message selected in the Inbox) to a directory on the PC.

- Merge all the saved Excel files into a single file - they're all formated exactly the same - data in about 20 columns, no column headers or anything, just standardised data.

- Save the merged file with a standard name (i.e. every time this is ran, it needs to have the exact same filename).

- Delete all the files in the directory apart from the merged file.

I know there's probably a fair bit of code online for doing the individual parts of this. Can I do both from within Outlook? It doesn't particularly matter if I need to do this in 2 steps - an Outlook macro to save the attachements, and an Excel macro to do the file merge etc.

Cheers.
 

Answer:Solved: MS Outlook / Excel macro - extract attachments and merge

16 more replies
Relevance 63.55%

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct
Job_xxx SUCCESS SUCCESS FAILED FAILED
Job_yyy SUCCESS SUCCESS SUCCESS RUNNING
Job_zzz MISSED MISSED CANCELLED SUCCESS

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.

Example:

- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

More replies
Relevance 62.73%

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

More replies
Relevance 62.73%

Good Afternoon,

I'm quite stuck trying to fix my code. I've scoured the internet looking for similar questions, but to no avail. I am trying to have excel create appointments in the outlook calendar and be able to run the macro multiple times without have duplicate appointments.

I could run my macro and create an appointment, which worked fine, but there was no routine to check if the appointment already existed. So I would have multiple appointment alerts for the same appointment if I ran the macro more than once. (If I were to add something and run the macro again to keep everything up to date).

So I tried to add a loop checking first the date of the appointment then the title (subject). However, now, I am getting the "Object doesn't support this property or method" error when I run the following code.

If anyone could help me with what the error refers to specifically in my code or any code I need to add or take out to make my loop work I would really appreciate it. Thank you very much in advance.
Code:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

Dim olCalendarFolder As Outlook.MAPIFolder 'use MAPI to loop through folder
Dim olMessage As Object 'items in calendar/inbox are messages
Dim olInboxMessages As Object
Dim olAppSession As Outlook.Namespace
Dim strSubject As String
Dim myDate As Date

Applic... Read more

More replies
Relevance 62.73%

Hi,

We have a spreadsheet that keeps track of when our computer warranties expire. Would like to run a macro using the warranty expired date to create an appointment in Outlook. That part I understand. The problem is that when I go to run the macro again, it creates a duplicate appointment if the appointment already existed. Very frustrating!

This is the code below, any help would be appreciated!!!! Thanks
Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, dStartTime As Date, dDuration#

For r = 4 To 10

sSubject = Sheet1.Cells(r, 1).Value
sBody = Sheet1.Cells(r, 4).Value
dStartTime = Sheet1.Cells(r, 3).Value
dDuration = 30
Set o = GetObject(, "Outlook.Application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Service Tag= " & sBody
ai.Subject = "Warranty Expires for Server " & sSubject
ai.Start = dStartTime
ai.Duration = dDuration
ai.Close olSave

Next r
End Sub
 

Answer:Solved: Excel Macro to Update Outlook Calendar Creates Duplicates

6 more replies
Relevance 62.73%

I am currently trying to create appointments in a calender i have created in outlook using information from an excel spreadsheet. I have copied several codes from other forums to help with this Macro but have only been able to add the appointments to my calender.

The code i am currently using is:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub
I also have the calender location but have no idea where it would need to fit into... Read more

Answer:Macro/VBE to create appointments in Outlook from an Excel Spreadsheet (Office 2003)

16 more replies
Relevance 62.32%

Hi all... I just joined this forum as past threads have been of great assistance... But now I have a immediate issue... I have wrote some code to pull info from a Excel report and create an Outlook appointment base on various commitment dates... This report is sent out daily therefore the macro is used daily... An issue that has risen is that everytime the macro is ran it creates duplicate appoinments which quickly bogg down the server... I saw an example on a closed thread and could not quite figure it out... Can anyone assist??? I am running 2003 versions of both Excel and Outlook and my code is as follow and thanks...

Sub ExceltoOutlookCommitDateSmithRenewalReport()
'
' ExceltoOutlookCommitDate Macro
' Load Excel Commit Dates into Outlook
'
' Keyboard Shortcut: Ctrl+o
'
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

If MsgBox(prompt:="OK if YES / CANCEL if NO", _
Buttons:=vbOKCancel + vbQuestion, Title:="HAS PAA BEEN FILTERED?") = vbOK Then
MsgBox "MACRO WILL NOW UPDATE OUTLOOK CALENDAR WITH PROCLAIM COMMIT DATES."

Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste

' Start at row 5
r = 5
Do Until Trim(Cells(r, 1).Value) = ""
If (Cells(r, 20).Value) = "" Then
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 2).Value
If (Cells(r, 5).Va... Read more

Answer:2003 Excel to Outlook Calendar appoinment VB macro creates duplicates appointments

Hi jayJay,

I'm not going to look through the whole code but what I think happens is that sinec you run the macro every time it will just add an appoitment, I think an options should be added, something like an extra column namen DONE and when the macro is run the cell in the column DONE should be given a value True() or a 1 to indicate it as done, so that when you run the macro again the column DONE cells where the valkue is not True() or is not 1 (dependign on waht you choose) is carried out.
This way you will avoid duplication.
Just an idea, happy coding.
 

1 more replies
Relevance 62.32%

Hi,

I need help with a macro. So far the macro is operating as it should , creating MS Outlook 2010 calendar appointments using data from an excel data spreadsheet (data base). The issue I&#8217;m having is that every time the macro is run for a new entry, duplicate calendar appointments are created for the already existing data in the data base. I would like to have the macro do a search to ensure an appointment does not exist before creating one. I have read a few threads with similar problems but have had major issues incorporating the solutions to resolve the issue I&#8217;m having.

I have attached the macro in txt. format for reference.

Thanks for your help.

kkfonty
 

Answer:Solved: Excel macro updating Outlook calendar is creating duplicate appointments.

Hi
You can use this function to check if an appointment exists based on the Start Day/Time

Code:

Public Function CheckAppointment(ByVal argCheckDate As Date) As Boolean

Dim oApp As Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim oApptItem As Outlook.AppointmentItem
Dim oFolder As Outlook.MAPIFolder
Dim oMeetingoApptItem As Outlook.MeetingItem
Dim oObject As Object

On Error Resume Next
' check if Outlook is running
Set oApp = GetObject("Outlook.Application")
If Err <> 0 Then
'if not running, start it
Set oApp = CreateObject("Outlook.Application")
End If

Set oNameSpace = oApp.GetNamespace("MAPI")
Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)

CheckAppointment = False
For Each oObject In oFolder.Items
If oObject.Class = olAppointment Then
Set oApptItem = oObject
If oApptItem.Start = argCheckDate Then
CheckAppointment = True
End If
End If
Next oObject

Set oApp = Nothing
Set oNameSpace = Nothing
Set oApptItem = Nothing
Set oFolder = Nothing
Set oObject = Nothing

End Function

For this to work you need to have Tools/References/Microsoft Outlook xx Object Library enabled.
Then alter your macro like this

Code:

Private Sub cmdOutApt_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = "&q... Read more

2 more replies
Relevance 61.91%

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

Answer:Access 2007 VB code to run Excel 2007 macro in active sheet

7 more replies
Relevance 59.86%

Hi guys I have this code which helps in exporting ppt from excel to powerpoint.

What Macro Does:
Exports Charts from excel work book to PPT

What I want it to Do:
can you please help me in editing the macro so it copies charts from worksheet.so what I mean it has to be edited in suc a way that it takes only charts/ Charts from active sheet and not whole file.
CODE:

Final Macro

Sub PushChartsToPPT()
'Set reference to 'Microsoft PowerPoint 12.0 Object Library'
'in the VBE via Tools > References...
'
Dim ppt As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptCL As PowerPoint.CustomLayout
Dim pptShp As PowerPoint.Shape

Dim cht As Chart
Dim ws As Worksheet
Dim i As Long

'Get the PowerPoint Application object:
Set ppt = CreateObject("PowerPoint.Application")
ppt.Visible = msoTrue
Set pptPres = ppt.Presentations.Add

'Get a Custom Layout:
For Each pptCL In pptPres.SlideMaster.CustomLayouts
If pptCL.Name = "Title and Content" Then Exit For
Next pptCL

'Copy ALL charts in Chart Sheets:
For Each cht In ActiveWorkbook.Charts
Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
pptSld.Select

For Each pptShp In pptSld.Shapes.Placeholders
If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
Next pptShp
If pptShp Is Nothing Then Stop

cht.ChartArea.Copy
ppt.Activate
pptShp.Select
ppt.Windows(1).View.Paste
Next cht

'Copy ALL charts ... Read more

Answer:need Help in editing the Macro to export Charts to PPt

Just remove the fro all loops and refer to the active worksheet
 

1 more replies
Relevance 59.45%

Hello,

I am trying to write a code for this workbook that will generate an email to my outlook account when the file is modified in anyway. I am not sure why my code is not working and welcome any assistance!

Additionally, it would actually be nice if the code were setup so that the email contained the information in columns B-F and was only generated when one of these columns is edited.

Thanks in advance!!

Ps. I tried to upload the file from my computer, but am getting a message that I am either not logged in/do not have access.
 

Answer:Solved: excel email to outlook code help

15 more replies
Relevance 59.04%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 58.63%

I have a multi-tab workbook and am trying to solve 2 issues with 1 macro.BACKGROUNDThe workbook contains a main tab with all of the 'entered info' that I am collecting. Based upon results in the main tab, I have 3 additional tabs that update. Essentially, the main tab is 'data entry' tab and the other 3 are 'report' tabs.WHAT I'M TRYING TO SOLVE FORAt some point, my main tab needs to have new rows inserted to handle additional data. I would like to insert new rows with all of the existing formulas/formatting etc with a macro (I know how to do it manually) AND, I would like to have the same thing happen in each of the additional 3 tabs. 1 macro that inserts a new line with formula/formatting into 4 tabs in the workbook at once.Is is possible? Anyone have suggestions? The main reasons that I'm trying this is for - simplicity - error prevention when someone takes over the effortThanks.

Answer:How to write Macro to add additional rows with existing form

It would be fairly simple to write a macro that loops through all sheets in a workbook and inserts rows, or even selects them all and does it in one step, but without more detail as to where the rows are supposed to go - in each sheet - there's not much we can offer.Please provide some specifics about your task.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 58.22%

Hi,

I'm new to the forum, so hopfully my question is easily answered. I've researched that I can use VBA code for the to solve my problem, but clearly have a lot to learn about VBA code and need a solution for my problem in the mean time.

I have attached the spreadsheet that I require help with.

I have an excel spreadsheet where I have drivers listed and number of days until their License & Registration expire (Columns F and H respectively). The drivers are identified by a driver number in column C.

What i'd like to do is have excel communicate with outlook and send me an appointment or email reminder when any driver is within 5 days of expiring. I also have other staff members that I would like the notification sent to also.

A question I have is: Will the reminders be sent automtically or will a macro need to be run? If it was to be done automatically, would it need to be upon opening the spreadsheet of would it just send the notification 5 days prior regardless.

Thanks in advance for your help.
 

Answer:VBA Code to notify me via outlook when excel dates are approaching expiry

I haven't taken a look at your sample file but this post has some code to create appointments in Outlook.
See if it can help you, you'll need to make changes.
And no, you will have to run the Excel file to trigger it.
Here's the link

http://forums.techguy.org/business-applications/739431-excel-macro-update-outlook-calendar.html

I'll see if i can find the time to look at your sample, but I don't want to make it late today
 

3 more replies
Relevance 57.81%

Im wondering if it is possible to do this before i start trying...

I recieve an email containing an excel sheet containing several peices of info such as:
Name
Surname
ID
Etc Etc... Now when i open this.. i need the information posted into specified columns in a new excel document where it will have
NAME | SURNAME | ID |
-------------------------------------------
STEVE NORTH 01874
Any help given appreciated.
 

Answer:Excel - Auto export data into specified excel sheet

6 more replies
Relevance 57.81%

Hi
I am trying to export data from word or excel form into a spreadsheet. I have tried the macro at http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html and it worked for all text fields but not the drop down list options that I have in my form. Can anyone please help/

I have never done this before, hence the struggle. Any help would be much appreciated.

I attach the word form and the excel form which also includes the excel spreadsheet that will collate the data for analysis.

I would prefer the form in word and have it set up as that indicated in the forum thread above but picking up the data from the drop down lists as well.

Thanks
LAD786
 

Answer:Export data from word or excel into Excel spreadsheet

Hi there, welcome to the forum.
It seems this post you added on August 12th hasn't given you much answers.
You mention the two files, OK, But what I don't understand
I am trying to export data from word or excel form into a spreadsheet. ...
Click to expand...

You probably forgot to attach the macro's with it, that is if you did anything with the link you mentioned.
If you could attach the files with the macros, I could take a look and see what I can do for you.

Another thinng From Word or Excel.

Which one is it going to be?
 

1 more replies
Relevance 57.81%

hello everybody

i hv created a tool bar by using macros in ms word 2003. i add some code for it in VB.i want to export that tool bar . because i have to send that tool bar to other sysems having ms word 2003.

can i export toolbar(created by using macro) as .dll or .exe?
sukumar
 

Answer:Solved: how to export a Tool bar (created by using macro) in ms word 2003

12 more replies
Relevance 56.99%

I am looking for help in writing a macro that will do the following.

With Excel 2003 open and the current workbook open. The macro should be able to save 3 variables from the current spreadsheet (some text, a value and a date ) The macro should open up Outlook 2003 as a task, attach the current workbook. In the Subject field of the task it should add the user's name, then the attached spreadsheet's filename then the text variable, then the value variable and finally the date.

Also it would be great if the Priority of the task could be set to High and the Start and Due dates set to 3 calendar days before the date variable.

Finally the macro would set a list of people the task is assigned to. The macro should NOT save the task as a check would need to be done manually.

I have tried unsuccessfully to write a macro in Outlook as I cannot seem to access the attachment filename or any data from the file. I have tried a macro in Excel which I can create a task and add some text to the Subject of the task but with no real progress on the attachment.

I have tried searching the net for help but a lot of code for Outlook is for the mail side of things rather than tasks and the little code snippets I have found have been of limited use. I have limited skills in programming Visual Basic and C so quite prepared to get my use code which does similar things
 

More replies
Relevance 54.94%

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 54.94%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 54.94%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 54.94%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 54.94%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 54.53%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 54.12%

I am attempting to find add-ons for Outlook 2010. This is what I need the add-on to do:
Export an email from Outlook onto our server, and into a job folder.
Save file with date email was received, not exported out of Outlook, and subject line.
Thanks
 

Answer:Outlook 2010/export emails from outlook to CSV folder with received date and subject

Why not drag and drop the email to the file system?
 

3 more replies
Relevance 54.12%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 54.12%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 54.12%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 54.12%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 54.12%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 54.12%

I was told that I can use the promo code for a computer I bought last month. That gets Windows 8 Pro for $15 instead of $40. I already used the code for that computer. Can I use it for other computers I have?

Answer:promo code re-use on additional computers?

NO.

6 more replies
Relevance 53.71%

Could you tell me how to export the contacts from Outlook 2007 to Outlook Express please?
 

Answer:Solved: Export contacts from Outlook 2007 to Outlook Express

14 more replies
Relevance 53.71%

Hi, my name is Kyle, I am new to this Forum.

I have seen a similar post which seems to be the solution to the problem I face, however, I cannot post on the topic for some reason - I would assume because it is marked as solved.

I needed to post in the topic for clarity, as I do not quite understand how to use the solution. I also cannot send PM's or Personal Emails and was told to not post Tech Questions within the Visitor Message section on a User Profile, so I have not been able to contact the user that offered the solution directly.

Therefore I am creating a new post.

I'm in the middle of capturing data - data in the form of emails dating back to 2009. This data is required to create a email database of all our previous and current clients.

Currently I am manually going through every email contained within a folder in my inbox and entering each email address into an excel sheet. This is incredibly time-consuming, as one might imagine and of-course I would like the quick-fix solution.

In the solution offered in the aforementioned post http://forums.techguy.org/business-applications/864373-solved-save-all-email-addys.html, there was reference made to software called NK2, which, by the sounds of it, I can somehow use to extract these email addresses and then import them into an excel database - problem being that I do not know how to use this program.

I would love for someone to guide me through the process!

The other solution that was offered, was a ma... Read more

Answer:Export all email addresses from Outlook \ Outlook inbox folder

Please help ASAP. I have to manually enter during waiting.
 

2 more replies
Relevance 53.71%

How do I export a single column of SQL into an Excel document?
 

Answer:SQL export to Excel

Example, using Excel 2007.
a) Open a new workbook
b) Click Data,Connections
c) Click Add, Browse for More
d) Select +New SQL Server Connection.odc
e) Enter server name and login credentials (windows or sql)
f) Select database e.g. pubs
g) Select table e.g employee
h) Select Finish, Close
i) Select Existing Connections, and select the Connection created earlier
j) Select PivotTable Report
k) Select the field(s) you wish to add to worksheet
l) Done
 

1 more replies
Relevance 53.71%

I am having an issue with end users who used to be able to export from IE 9 to Excel 2010 on a Windows XP machine. When they were upgraded to Windows 7 and IE 11 the menu option was no longer there.  I added it back in with a registry fix, but
now when they right click and export to Excel, they get an error message that Excel could not retrieve the data along with a URL box that says http:///
Any ideas?  The klugy workaround is to copy the link, paste it into the empty box, click go, input their credentials again, then finally the data comes over.  Not a good workaround compared to before.

Thanks
Chaim

More replies
Relevance 53.71%

I have excel 2003 in my computer. It can not export to citrix. will anyone help How to solve this? Thanks
 

Answer:Excel Can not export

More explanation please. What do you mean you cannot export from Excel to Citrix? Are you using Excel from Citrix or on your local PC? What do you mean by export?
 

2 more replies