Please help - VBA to save email to drive

[bstevens]

I've been using the macro below (I got this from the Outlook VBA help
file - I'm not a heavy duty coder) to save selected email to a folder
on a hard drive. Users will be saving hundreds and hundreds of emails
in this manner, so I'm trying to automate it as much as possible.

I need to change two things:

1. Instead of naming the file using the "Subject:" string, open a box
so the user can name it.

2. In #1, the user will always enter a 6-digit number, e.g. "987021"
as the file name. A critical point is that over several months, the
user will save several files with the same name. When that happens,
the new file can NOT "replace the existing file by the same name."
There are two possible approaches to this:

One approach is to allow multiple files to be saved with the same
name. Maybe something like 987021-2, 987021-3, and so on, or whatever
could work. The user can NOT enter this "-2" or "-3" suffix, it needs
to be automatic. In any case, the user can only enter the 6-digit file
name.

A better approach would be where if the filename already exists, it
would catenate the new file to the existing one, instead of creating a
new one. I have utterly no idea how to do that. This approach would
actually be MUCH more preferable than the first one.

These two fixes would be enormously appreciated, and would save
several people a huge quantity of time and work.

Please reply only on the newsgroup.
Thanks,

Bill S.

Here's the current code:
******************************************
Sub saveemail()

Dim myItem As Outlook.Inspector
Dim objItem As Object
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector
If Not TypeName(myItem) = "Nothing" Then
Set objItem = myItem.CurrentItem
strname = objItem.Subject
'Prompt the user for confirmation
Dim strPrompt As String
strPrompt = "Are you sure you want to save the item? If a file
with the same name already exists, it will be overwritten with this
copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
objItem.SaveAs "S:\Funding Team\Communication\Region
1\Calendar Year 2007\" & strname & ".txt", olTXT
End If
Else
MsgBox "There is no current active inspector."
End If

End Sub

 

[Eric Legault [MVP - Outlook]]

You can use the InputBox function to prompt the user to input a string that
you can retrieve.

If you want to auto-generate a filename, you'd have to use a recursive loop
where you first check to see if the file exists (use the Dir statement); if
it doesn't, increment a numeric variable and continue the loop until Dir
returns an empty string - indicating the file doesn't exist.

Note that you can't automatically merge two files together unless they're
ASCII based (.txt, .html, etc.) - but then you'd still have to manually parse
and edit the text with code.

 

[bstevens]

Thanks very much, Eric. If the files can't be merged together, that's
fine. I can't figure out how to do what you describe, though.

Ron

 

[Eric Legault [MVP - Outlook]]

Here's a sample loop that illustrates looking for a file and appending a
number to the filename until a file with that name doesn't exist:

If Dir(strFolder & strFileName & strExtension, vbNormal) <> "" Then
Do
intX = intX + 1
strFileName = strFolder & strFileName & intX & strExtension
Loop Until Dir(strFileName, vbNormal) = ""
End If

 

[bstevens]

Thanks, Eric. I'll try to work that into the code.

Can you possibly help me out with the other issue - letting the user
input the filename, instead of automatically using the Subject: line?
If I can't do that, I can't use the code below.

Thanks a heap,
Ron

 

[Eric Legault [MVP - Outlook]]

Just use the InputBox function - that'll present a dialog that will allow the
user to type in the file name they want. The function returns a string. You
can always put your cursor on the function name and press F1 to see the help
reference for it.

--
Eric Legault - Outlook MVP, MCDBA, MCTS (SharePoint programming, etc.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/

Thanks, Eric. I'll try to work that into the code.

Can you possibly help me out with the other issue - letting the user
input the filename, instead of automatically using the Subject: line?
If I can't do that, I can't use the code below.

Thanks a heap,
Ron

Here's a sample loop that illustrates looking for a file and appending a
number to the filename until a file with that name doesn't exist:

If Dir(strFolder & strFileName & strExtension, vbNormal) <> "" Then
Do
intX = intX + 1
strFileName = strFolder & strFileName & intX & strExtension
Loop Until Dir(strFileName, vbNormal) = ""
End If

--
Eric Legault - Outlook MVP, MCDBA, MCTS (SharePoint programming, etc.)
Try Picture Attachments Wizard for Outlook:http://www.collaborativeinnovations.ca
Blog:http://blogs.officezealot.com/legault/

Thanks very much, Eric. If the files can't be merged together, that's
fine. I can't figure out how to do what you describe, though.

Ron

On Jul 3, 10:22 am, Eric Legault [MVP - Outlook]
You can use the InputBox function to prompt the user to input a string that
you can retrieve.

If you want to auto-generate a filename, you'd have to use a recursive loop
where you first check to see if the file exists (use the Dir statement); if
it doesn't, increment a numeric variable and continue the loop until Dir
returns an empty string - indicating the file doesn't exist.

Note that you can't automatically merge two files together unless they're
ASCII based (.txt, .html, etc.) - but then you'd still have to manually parse
and edit the text with code.

:
I've been using the macro below (I got this from the Outlook VBA help
file - I'm not a heavy duty coder) to save selected email to a folder
on a hard drive. Users will be saving hundreds and hundreds of emails
in this manner, so I'm trying to automate it as much as possible.

I need to change two things:

1. Instead of naming the file using the "Subject:" string, open a box
so the user can name it.

2. In #1, the user will always enter a 6-digit number, e.g. "987021"
as the file name. A critical point is that over several months, the
user will save several files with the same name. When that happens,
the new file can NOT "replace the existing file by the same name."
There are two possible approaches to this:

One approach is to allow multiple files to be saved with the same
name. Maybe something like 987021-2, 987021-3, and so on, or whatever
could work. The user can NOT enter this "-2" or "-3" suffix, it needs
to be automatic. In any case, the user can only enter the 6-digit file
name.

A better approach would be where if the filename already exists, it
would catenate the new file to the existing one, instead of creating a
new one. I have utterly no idea how to do that. This approach would
actually be MUCH more preferable than the first one.

These two fixes would be enormously appreciated, and would save
several people a huge quantity of time and work.

Please reply only on the newsgroup.
Thanks,

Bill S.

Here's the current code:
******************************************
Sub saveemail()

Dim myItem As Outlook.Inspector
Dim objItem As Object
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector
If Not TypeName(myItem) = "Nothing" Then
Set objItem = myItem.CurrentItem
strname = objItem.Subject
'Prompt the user for confirmation
Dim strPrompt As String
strPrompt = "Are you sure you want to save the item? If a file
with the same name already exists, it will be overwritten with this
copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
objItem.SaveAs "S:\Funding Team\Communication\Region
1\Calendar Year 2007\" & strname & ".txt", olTXT
End If
Else
MsgBox "There is no current active inspector."
End If

End Sub