Solved

Buttons to Save As and Mail document

Posted on 2002-05-15
19
256 Views
Last Modified: 2007-12-19
I would like to automate a document with two buttons located within the document contents.
One button, labeled "SAVE YOUR COPY" will act just like clicking File, Save As
Another button labeled "SUMBIT FORM" will act as if you clicked File, Send, Email Recipient.


0
Comment
Question by:gizmorama
  • 7
  • 5
  • 3
  • +2
19 Comments
 
LVL 5

Expert Comment

by:nfroio
ID: 7011264
What program, version, Operating System, etc.?

0
 

Author Comment

by:gizmorama
ID: 7011291
Word 97, Various OS as it will be a document available to customers via web.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7011395
Hi gizmorama,

-in a word document
-right click on a toolbar to popup the list of toolbars check the toolbox
-now you can drag two buttons to the document
-right click the first one and adjust the properties like name call it save
-to this also for the second one and call it submit or send

-then doubleclick save and add the code something like
sub cmdSave_Click()
  Activedocument.Save
end sub

-then doubleclick send and add the code something like
sub cmdSend_Click()
  Activedocument.Sendmail
end sub

-save this and return to the document

:O)Bruintje
0
 

Author Comment

by:gizmorama
ID: 7011426
right click on a toolbar to popup the list of toolbars check the toolbox -- no such selection "toolbox" from list.
0
 
LVL 5

Expert Comment

by:nfroio
ID: 7011446
He means, View>Toolbars>Control Toolbox

and then just grab, for instance the button labled, "Control Button". and then follow his comments.
0
 

Author Comment

by:gizmorama
ID: 7011482
Cool. Need to do something about a failure message when Save As is clicked, but user decides to cancel.
Increased points for this.
0
 
LVL 5

Expert Comment

by:nfroio
ID: 7011527
sadly, VB is definately not my forte... bruintje should be able to help you..
0
 

Author Comment

by:gizmorama
ID: 7011716
oh, forgot...also need to populate to: field of mail.
and
Save funtion OK, need Save As though. We are getting close.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7011972
well i'm back.....or at least i'm looking at a keyboard again

-so you need the saveas funtion?
-is the user the one who must give a filename or something?
-and for the mail?
-a mail button and there must a chance to fill in the mail address or so?

could you please give the requirements list again
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:gizmorama
ID: 7014161
SAVE A COPY button does exactly like SaveAs on menu. User must give file name and location not get "did not complete" error message if user cancels.

SUBMIT button emails active document to pre-determined address (will always be same address.)

0
 
LVL 2

Expert Comment

by:macbone2
ID: 7015267
gizmorama,   I send by email, to sopme asssociate companies an excel sheet with a couple of buttons in. One of the buttons is to save a copy of the sheet on their local drive. Code as below. It offers the user the choice to save or not. If the choice is to save, the program first checks if the folder exists and creates it if not (MkDir) then save the file. BEWARE local administrator constraints. I also give access to Excel files on a controlled server where some users can respond by clickin a button, which automatically sends email. This only works for OUTLOOK users, and there are some constraints in that certain library references must be selected based on the version of outlook installed on the pc or at the site. If you want detail of the code comeback. (it allows flexibility on To... field SentOnBehalfOf...  field, Mail text and mail subject)

Sub SaveLocal()
Dim msg As String, ttle As String
Dim Savefile As String
Dim P1Dir As String
P1Dir = "c:\Priority One Enquiries"
Savefile = P1Dir & "\Enquiry " & Trim(Range("AQ6").Value)
ttle = "* *  S A V I N G   E N Q U I R Y  * *"
msg = _
"The Enquiry File will be saved on your local [C:] drive as :- " & Chr(13) & _
Savefile & Chr(13) & _
"It will be placed in a folder called  " & P1Dir & Chr(13) & _
"If this folder does not already exist, it will be created." & Chr(13) & _
"If you don't want to  save a copy, press Cancel, otherwise click 'OK'."
intResponse = MsgBox(msg, vbCritical + vbOKCancel, ttle)
If intResponse = vbCancel Then
Exit Sub
End If
On Error Resume Next
MkDir P1Dir
If Err.Number = 0 Or Err.Number = 75 Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Savefile, ReadOnlyRecommended
Application.DisplayAlerts = True
Else
Exit Sub
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0
 
LVL 2

Expert Comment

by:macbone2
ID: 7015275
gizmorama,   I send by email, to some asssociate companies an excel sheet with a couple of buttons in. One of the buttons allows the user to save a copy of the sheet on their local drive. Code as below. It offers the user the choice to save or not. If the choice is to save, the program first checks if the folder exists and creates it if not (MkDir) then saves the file. BEWARE local administrator constraints.
##########  MACRO CODE   ############
Sub SaveLocal()
Dim msg As String, ttle As String
Dim Savefile As String, P1Dir As String
P1Dir = "c:\P1_Enquiries"
Savefile = P1Dir & "\Enquiry " & Trim(Range("AQ6").Value)
MsgTitle = "* *  S A V I N G   E N Q U I R Y  * *"
msg = _
"The Enquiry File will be saved on your local [C:] drive as :- " & Chr(13) & _
Savefile & Chr(13) & _
"It will be placed in a folder called  " & P1Dir & Chr(13) & _
"If this folder does not already exist, it will be created." & Chr(13) & _
"If you don't want to  save a copy, press Cancel, otherwise click 'OK'."
intResponse = MsgBox(msg, vbCritical + vbOKCancel, MsgTitle)
If intResponse = vbCancel Then
Exit Sub
End If
On Error Resume Next ' if directory already exists
MkDir P1Dir   'Err.Number 75 = directory alrady exists
If Err.Number = 0 Or Err.Number = 75 Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Savefile, ReadOnlyRecommended
Application.DisplayAlerts = True
Else
Exit Sub
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I also give access to Excel files on a controlled server where some users can respond by clicking a BUTTON, which automatically sends email. This only works for OUTLOOK users, (BUT can be set up for other mail users using CDO) and there are some constraints in that certain library references must be selected based on the version of outlook installed on the pc or at the site. If you want detail of the code comeback. (it allows flexibility on To... field SentOnBehalfOf...  field, Mail text and mail subject)

Geoff (macbone2)
0
 

Author Comment

by:gizmorama
ID: 7028008
All this brings me to ask:
Is there some sytax that can be added to the mailto:name@domain piece of code? Maybe this email thing can all be resolved with a little HTML which would simply activate the MAPI default client.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7135037
that can be done through the shell execute

-in your word document
-open the vb editor with ALT+F11
-then insert a new module
-then paste the code

Option Explicit

'************ Code Start **********
Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

'***************Usage Examples***********************
'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL:          ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
'                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
'                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
           
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********

-now save this
-on your document doubleclick the button to mail
-in design mode this will bring up an empty event handler for the button
-now in the eventhandler paste this line

fHandleFile "mailto:me@somewhere.com", WIN_NORMAL

-that's all close and save the editor
-back in your document test it out
-not as neat like a simple html link but almost as easy to bring up the default mail client with a to address

HAGD:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7214980
Hello gizmorama

time to clean up
if not stated otherwise

my recom will be
-PAQ
-points to bruintje
-this will be finalized with no further update (19.08.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
0
 

Author Comment

by:gizmorama
ID: 7215083
Last piece of code works great to send mail, but does not attach current document or form.

Giz
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7400504
then we'll have a PAQ and refund
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 7428334
PAQ'd and points refunded as proposed

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 2

Expert Comment

by:macbone2
ID: 7432898
Gizmorama, I didn't know this question had been outstanding so long. I use an Excel form with the code below in to send a copy of the form (after it has been saved) to numerous recipients. It's all controlled by VBA macros which eventually pass the name of the sender (SentOnBehalfOf) the recipient, the subject, the path and file names of the attachments, and the body of the email. It can also request (or not) a delivery report. This runs inside Excel forms, but the logic should be useable for Word documents etc.
Geoff

Sub Send_Msg_Atch(SOBON, DistName, MailTextLine, EmailSubject, AttachFile, Attachfile2)
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
On Error GoTo ErrorReport
With objMail
    .OriginatorDeliveryReportRequested = True
    .ReadReceiptRequested = False
    .SentOnBehalfOfName = SOBON
    .Importance = olImportanceHigh
    .To = DistName
    .Subject = EmailSubject
    .Body = MailTextLine
    .Attachments.Add AttachFile
    .Attachments.Add Attachfile2
   '.Save
    .Send
End With
Re_Set:
Set objMail = Nothing
Set objOL = Nothing
Exit Sub
ErrorReport:
'Stop
If Err.Description = "Outlook does not recognize one or more names. " Then
CLICKRESP = MsgBox("The email address for this Associate - " & DistName _
    & " - is not a valid email address. Please check it.", vbOKCancel, _
    "EMAILING HOTEL SALES TEAMS")
GoTo Re_Set
End If
If Err.Description = "There must be at least one name or distribution list in the To, Cc, or Bcc box." Then
CLICKRESP = MsgBox("There is no email address for this hotel - " & UCase(HName) _
    & " Please remember to FAX the enquiry.", vbOKOnly + vbExclamation, _
    "EMAILING HOTEL SALES TEAMS")
GoTo Re_Set
End If
CLICKRESP = MsgBox("An unexpected error has occurred. When sending to " & UCase(HName) & _
"  The description is - " & Err.Description & Chr(13) & _
    "Please remember to FAX the enquiry." & Chr(13) & _
    "Please inform I S  about the problem", vbOKOnly + vbExclamation, _
    "** ERROR EMAILING HOTEL SALES TEAMS **")
GoTo Re_Set
End Sub
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now