Manipulate Excel using Outlook VBA

[Mister H]

Hi All:

I am working on a code in Outlook and was wondering a few things regarding opening and manipulating an Excel Attachment.

1) Can I rename a tab on an Excel worksheet? Example select the sheet titled Journal 2 and rename it to Journal

2) How do I physically select the sheet titled Journal? In order for the macro “DelZeroRows” to function properly the sheet titled JOURNAL must be the active sheet. In most cases it is but in some cases another sheet is the active one when the file is open. This line seems to work for Printing purposes but does not seet to make JOURNAL the active sheet:

Set xlwkbk = xl.Workbooks.Open(fileName)
Set xlwksht = xlwkbk.Sheets("JOURNAL")
xl.Run "DelZeroRows"

3) Lastly below is a snippet of the code I am using. Can the changes that were made to the Excel Attachment be Saved so that when I open the email next time I don't have to make the changes again? eg Run the macro DelZeroRows again


xlwksht.PrintOut
xlwkbk.Close False
Set xlwkbk = Nothing
End If
Next msgAttach


‘CAN I SAVE THE CHANGES THAT WERE MADE TO THE EMAIL ATTACHMENT BEFORE IT IS MOVED??????

Msg.Move Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("Move to This Folder")


THANKS to anyone that can assist or direct me to another post.
Mark http://www.vbaexpress.com/forum/images/icons/icon7.gif

[Mister H]

Hi All:

I am working on a code in Outlook and was wondering a few things regarding opening and manipulating an Excel Attachment.

1) Can I rename a tab on an Excel worksheet? Example select the sheet titled Journal 2 and rename it to Journal

2) How do I physically select the sheet titled Journal? In order for the macro “DelZeroRows” to function properly the sheet titled JOURNAL must be the active sheet. In most cases it is but in some cases another sheet is the active one when the file is open. This line seems to work for Printing purposes but does not seet to make JOURNAL the active sheet:

Set xlwkbk = xl.Workbooks.Open(fileName)
Set xlwksht = xlwkbk.Sheets("JOURNAL")
xl.Run "DelZeroRows"

3) Lastly below is a snippet of the code I am using. Can the changes that were made to the Excel Attachment be Saved so that when I open the email next time I don't have to make the changes again? eg Run the macro DelZeroRows again


xlwksht.PrintOut
xlwkbk.Close False
Set xlwkbk = Nothing
End If
Next msgAttach


‘CAN I SAVE THE CHANGES THAT WERE MADE TO THE EMAIL ATTACHMENT BEFORE IT IS MOVED??????

Msg.Move Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("Move to This Folder")


THANKS to anyone that can assist or direct me to another post.
Mark http://www.vbaexpress.com/forum/images/icons/icon7.gif

Hi:

I just thought I would let you know that I still require an answer to # 3 RE: SAVing changes to the orig email message and attachment.

Regarding the other 2 questions I have found answers. Here they are just in case any others are looking for something similar:

1) renaming a TAB
xlwkbk.Sheets("Journal 2").Name = "Journal"

2) physically select the sheet titled Journal
xlwkbk.Sheets("Journal").Select

3) UNANSWERED

From what I am finding it seems that # 3 may not be a doable thing. Can anyone confirm this for me so I can stop the serch. If there is a way of doing it I would appreciate a redirection towards the answer.

THANKS,
Mark

[JP2112]

When you edit an attachment without first saving it, it ends up in a local temp folder -- open a spreadsheet attached to an email and press F12 to see the location.

If you open up another email with the exact same attachment, however, it will overwrite the one in the temp folder.

So what you need to do is save the attachment somewhere first before editing it. Moving the email doesn't help because the file opens in the same temp folder each time.

[Mister H]

Hi JP:

THANKS for the input. Appreciated. I am not quite certain that I am following your suggestion as (I think) the code I am using is saving the attachment prior to making the changes to it, but when I reopen the message the changes are not saved.

Here is what I am using (a lot of the code provided by you :friends: )


Sub Print_And_Move_Emails()

On Error GoTo ErrorHandler
Dim i As Long
Dim Folder As Outlook.MAPIFolder
Dim itm As Object
Dim Msg As Outlook.MailItem
Dim msgAttachments As Outlook.Attachments
Dim msgAttach As Outlook.Attachment
Dim xlwkbk As Excel.Workbook
Dim xlwksht As Excel.Worksheet
Dim fileName As String

Set Folder = Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("1) To be Printed")

If Folder Is Nothing Then GoTo ProgramExit
For i = Folder.Items.Count To 1 Step -1

If TypeName(Folder.Items(i)) = "MailItem" Then
Set Msg = Folder.Items(i)
Set msgAttachments = Msg.Attachments

If msgAttachments.Count > 0 Then

For Each msgAttach In msgAttachments
If Right$(msgAttach.fileName, 3) = "xls" Then


‘Message attachment is being Saved to a Temp folder
fileName = Environ("temp") & "\" & msgAttach.fileName
msgAttach.SaveAsFile filename


If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
End If
Set xlwkbk = xl.Workbooks.Open(fileName)
Set xlwksht = xlwkbk.Sheets("JOURNAL")

‘Macro contained in the attachment is run that Deletes the unnecessary rows
xl.Run "DelZeroRows"

Do I need to Save the attachment again in order to save the changes???

xlwksht.PrintOut

xlwkbk.Close False
Set xlwkbk = Nothing

End If
Next msgAttach

Msg.Move Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("2) To be Verified")


End If
End If
Next i

ProgramExit:
'Close files and applications if necessary
If Not xlwkbk Is Nothing Then xlwkbk.Close False
If Not xl Is Nothing Then xl.Quit

'Delete the file if necessary
If fileName <> "" Then Kill fileName
Exit Sub

ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit

End Sub



After the file has moved and I reopen the attchment all the rows that were deleted using the DelZeroRows macro are back. Note: If I stop the code and then goto my Temp folder and view the Excel files the changes are there.

How (IF) can the above code be altered to assure all changes are saved.

THANKS JP or anyone else that can assist,
Have a GREAT day,
Mark

[Charlize]

Change xlwkbk.Close Falsewithxlwkbk.Close True.
This will save the changes you made (I think.).
Charlize

[Mister H]

Hi Charlize:

THANKS for your reply unfortunately that did not work for me :(

I changed it as you suggested but when I went and checked the email the rows that were deleted prior to printing were still in the excel attachment.

I was really hoping that the solution was as simple as you had suggested but No Such Luck? :banghead:

Any other ideas? Anyone?

THANKS,
Mark

[Charlize]

- The modified attachment is saved to your disk.
- When you open your email, you don't open the saved attachment but the original one.
- You could at the newly saved attachment to the original message prior to moving.
- With msg.attachments.add filename you can add the modified file.
- Then you save the message prior to moving.

Msg.Save
Msg.Move Outlook.Session.GetDefaultFolder(olFolderInbox).Folders("2) To be Verified")

Just an idea of course.

Charlize

ps.: little remark : I wouldn't use the name filename as variable, maybe change it to myfilename. It's probably not a keyword but seems a very common name to me (could be me off course :) ).

[Mister H]

THANKS Charlize... AAs soon as I get a chance I will try out your suggestions and see if I have any luck implementing them. I will rport back I just wanted to start with a THANKS...

Have a GREAT weekend,
Mark
:beerchug: