Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-16-2020, 10:32 PM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Default Command button. To multi command

Hi Guys I'm a return visitor from years back.

So consider me a noob. Especially in macro/vba world.


I'm struggling with my form
It's a command button that launches to save template file with original title and adds date and time in plain text format 000000_0000.

Then to save a pdf file locally in the 1 folder and attach it to a email with predefined recipients and body text.

Currently it's doing everything accept adding date and time it appears to be replacing itself and converting the file as it.

I have tried countless times and had to resort to a foundation script many times due to messing it up royally.

Code below

Private Sub sendreport_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim strFileName As String
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0) ' 0 = olMailItem
Set Doc = ActiveDocument
strFileName = Replace(Doc.FullName, ".docm", ".pdf")
Doc.ExportAsFixedFormat OutputFileName:=strFileName, _
ExportFormat:=wdExportFormatPDF
With EmailItem
.Subject = "Security handover report, for duty completion: 00/00/00 - 0000-0000"
.Body = "* SECURITY INSTRUCTION = Change date/time above in end of subject line + Add designation/name to signature area below + DELETE THIS LINE OF TEXT" & vbCrLf & _
"Dear Team Member," & vbCrLf & _
" Please find attached the Security Handover Report' for the completed duty period. " & vbCrLf & _
" TYRONE= TEST: AUTO Stationary is applied Y= EDIT VBA Remove this text & UPDATE .-TO-= Email Recipients list, Master file be kept in OneDrive with a desktop shortcut link on PC desktop. Fix to save with automated in both local store file and one attached<<< REMOVE-AMMEND THIS TEXT Before going live"
.To = "tyrone@email.com.au; main@emails.com.au "
.Importance = 1 ' olImportanceHigh
.Attachments.Add strFileName
.Display
End With
Application.ScreenUpdating = True



Set Doc = Nothing
Set EmailItem = Nothing
Set OL = Nothing
MsgBox "Please change the *DATE* & *SHIFT TIME* in the subject line of the email. Add your Designation/NAME to the signature in the email bottom and hit send... Word is going to save and close hit OK."
ActiveDocument.Close SaveChanges:=wdSaveChanges
End Sub


I appreciate any help regards.
Reply With Quote
  #2  
Old 08-16-2020, 11:40 PM
gmayor's Avatar
gmayor gmayor is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

While would have used the code below to create the message, which will retain the user's signature, are you saying that you want the attachment that you have already attached to the message to include the date and time from the subject of the message? If so, that's not going to happen.


If you want the date and time in your pdf name then you should get the user to enter the time and date in your userform and apply them to the pdf before you create the message (and don't try and use the '/' character in that filename).


As you are using a userform to create the message, there is no point in getting the user to mess around with the created message. That message should be complete.



Code:
Private Sub sendreport_Click()
Dim OL As Object
Dim EmailItem As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Range
Dim Doc As Document
Dim strFileName As String
    Application.ScreenUpdating = False
    Set Doc = ActiveDocument
    Doc.Save
    strFileName = Replace(Doc.FullName, ".docx", ".pdf")
    Doc.ExportAsFixedFormat OutputFileName:=strFileName, _
                            ExportFormat:=wdExportFormatPDF

    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(0)    ' 0 = olMailItem

    With EmailItem
        .To = "tyrone@email.com.au; main@emails.com.au "
        .Importance = 1    ' olImportanceHigh
        .Subject = "Security handover report, for duty completion: 00/00/00 - 0000-0000"
        .Attachments.Add strFileName
        Set olInsp = EmailItem.GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        oRng.Collapse 1
        oRng.Text = "* SECURITY INSTRUCTION = Change date/time above in end of subject line + Add designation/name to signature area below + DELETE THIS LINE OF TEXT" & vbCrLf & _
                    "Dear Team Member," & vbCrLf & _
                    " Please find attached the Security Handover Report' for the completed duty period. " & vbCrLf & _
                    " TYRONE= TEST: AUTO Stationary is applied Y= EDIT VBA Remove this text & UPDATE .-TO-= Email Recipients list, Master file be kept in OneDrive with a desktop shortcut link on PC desktop. Fix to save with automated in both local store file and one attached<<< REMOVE-AMMEND THIS TEXT Before going live"
        .Display
    End With
    Application.ScreenUpdating = True

    Set Doc = Nothing
    Set EmailItem = Nothing
    Set OL = Nothing
    Set wdDoc = Nothing
    Set olInsp = Nothing
    Set oRng = Nothing
    MsgBox "Please change the *DATE* & *SHIFT TIME* in the subject line of the email. Add your Designation/NAME to the signature in the email bottom and hit send... Word is going to save and close hit OK."
    ActiveDocument.Close SaveChanges:=wdSaveChanges
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 08-17-2020, 04:57 AM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Default

Thankyou. GMayor.
I am aware if I wanted to have the subject auto date and time a Outlook embedded form template.

I am sure I can use the instruction to make sure guys change this along with adding their name to the signature in the email. I trialled my vide and the signature didn't add to the new email.

You where correct about wanting to have this filename-date-time.pdf save in primary document directory whilst adding the new email with recipient subject and body prefilled as new email that also when making the new email it uses the outlook default email signature.

I am work but I will look in the next 12 hrs. I will try explain my intention a bit better without being on the run and pressed for time. Appreciate your time.
Reply With Quote
  #4  
Old 08-17-2020, 03:33 PM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Default

Hello again,
I have attached the creenshots of the Word Doc in DEV mode so you can see what i am working from. or tyring to achieve better.

**Desired action***
When the DOCM File is filled out, it saves itself as a DOCM file It also creates a PDF in host directory with a namechange adding to the origional filename the DATE&TIME stamp from the system at time of execution and the same file is then opened in outlook as a attachment using the signature on the system it completes the prefefined changes subject recipients and body text according to the document being executed.
There are 3 primary shifts per day adding a date and time stamp to these will allow recipients to know that at that time it was from the shift than has just been completed. Keeping a copy of the converted PDF with time and date stamps locally and sending the same file via email. But using the DOCM file as a template that keeps saved data each report sendt would be good to enable Autosave into one drive on this document however i know that isint possible so a failsafe will be the standard are you sure you want to exit withoit saving should be enough. I have tried to work 2 marcos together to achieve the desired results without any joy

So, my reasoning for somehow having the code auto save and change the name on save is to auto date and time of current save. the document/form template i have created is content control heavy, it has a date and timetable that are date picker & shift time dropdown ranges.

The biggest issue is I am and dealing with security officers that are illiterate on PC let alone how-to database or index files properly and having them have to manually enter something often does not even happen. we have had report emails sent without files. we have had wrong files sent to wrong email groups and all kinds of combination of lack in continuation in care and uniform works produced.

From the Command button either the document can read the date and time cells and add them to the file name, or get the date/time from the system and apply it in Simple format 000000_0000 to the tail of the file name and use this to save the file in the directory keeping 1 Word saved file updated with every report sent, Save a PDF version of the same report generated into the primary directory and to action a email with the pre-defined email template. I am hoping user selectable form controls make the job quicker and simple whilst governing the formats, fonts and formulas guys enter data into the reports because after a few weeks our documents were getting to 12 pages long all kinds of font types, colors, highlights and more. Keeping it that simple that when they hit the button it does everything for them and uses the systems default email signature applied.

Meaning they click to open a shortcut to template edit and hit send change date/time in subject add their name and hopefully that is not too hard everything else is done.

I hope this better explains the need.
The guys struggle with saving as PDF and making name changes everyone has a different way to format things in thier head i am trying to eliminate the lack of governance in making that happen.


ITS BEEN ABOUT 10 Years since i did any of this Macro, VBA stuff.
Kind Regards Tyrone
Attached Images
File Type: jpg IMG 1.jpg (172.2 KB, 31 views)
File Type: jpg IMG 2.jpg (154.4 KB, 30 views)
File Type: jpg IMG 3.jpg (135.3 KB, 30 views)

Last edited by Taylormations; 08-17-2020 at 05:40 PM. Reason: Additional information. And share my attempts to remedy my problem
Reply With Quote
  #5  
Old 08-17-2020, 08:13 PM
gmayor's Avatar
gmayor gmayor is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Can you attach the actual document rather than a screen shot?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 08-17-2020, 09:31 PM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Angry Additional Information.

Thanks, I was not aware, I could attach. please find the project file attached. along with a print screen and how it is desired to function. Again many Thanks think I'm Rye getting Neck from researching and code modifications yet I know it some basic fundamental core code. but do you think I can figure it out... NOPE hahaha.

FYI Gmayor i couldnt help but notice it appears your a strong contibutor and of great value in knowedge and assistance accross the forum and other places. Thank you
Attached Images
File Type: jpg Untitled-1.jpg (81.5 KB, 28 views)
Attached Files
File Type: docm Sec.Handover.Report_DOCM - Copy.docm (151.8 KB, 8 views)

Last edited by Taylormations; 08-17-2020 at 11:30 PM. Reason: Additional information.
Reply With Quote
  #7  
Old 08-18-2020, 01:11 AM
gmayor's Avatar
gmayor gmayor is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

I have made some changes which I think will address your requirements. If the e-mail account has a default signature then that signature will be retained.

The completed document and pdf are stored in the user's Documents folder. There should be no need for the user to touch the e-mail message when you are happy with the message content.
Attached Files
File Type: docm Sec.Handover.Report_DOCM - Copy.docm (158.3 KB, 11 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 08-18-2020, 02:14 AM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Thumbs up So Close, i near kissed my monitor HAHA

You, sir, are a legend. This appears a smarter and tidier way of doing it Now it's even more foreign than I know however code ran much smoother and tidier.

My only concern now is that the original DOCM is not Saved on itself to be opened by the next user shift retaining and containing all content the shift prior had entered. Is there a way to have this save the original file to reload from because the saved DOCX copies in the documents don't want to open and sadly the main idea is one document to act as a template I don't mind if the template file can be used and used to overwrite itself.

I am happy if it has to save the Word DOCX files because of the automated file naming and conversion process. To add a command to update the template original file would be greatly appreciated otherwise the guys will only keep opening a blank template not an updated template if you know what I mean. Because each shift of information rolls over to the next shift then is amended during that one for the third rotation for the day.

I'm stuck with a few members that think they know what's best and everything about computers and compliance and then the clients complaining about how out of control a simple handover is. this way it's so locked down it's foolproof.


Again thank you for your time and on getting this right I'm happy to make a contribution for your time.

Regards Tyrone
Reply With Quote
  #9  
Old 08-18-2020, 04:20 AM
gmayor's Avatar
gmayor gmayor is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Change the oDoc.SaveAs2 line to
Code:
oDoc.SaveAs2 FileName:=strPath & strFileName & ".docm", Fileformat:=wdFormatXMLDocumentMacroEnabled
to save a working version of the saved document.


You may find https://www.gmayor.com/insert_content_control_addin.htm useful when working with content controls
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 08-22-2020, 06:11 PM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Exclamation

Gmayor, Its been working like a treat and well almost everyone was happy to accept the members who thought the document took 10 minutes to run the command. HAHA was only because it opened under and the flashing orange outlook taskbar button didn't umm yea hahaha.

The question does the VBA basics work across the board for all software due to it being a language that is being used in said software its the command and references that are software specific and the executed outputs. If that makes any sense? the example just to save an excel file on itself and convert and send a single spreadsheet page is pretty much the same core principals?


BTW is the fund me on your Private domain active?
Reply With Quote
  #11  
Old 08-22-2020, 07:59 PM
gmayor's Avatar
gmayor gmayor is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

VBA does work in Excel but the syntax will change, however the principles are similar.
The donation buttons on my web site certainly work
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #12  
Old 09-14-2020, 02:39 AM
Taylormations Taylormations is offline Command button. To multi command Windows 10 Command button. To multi command Office 2016
Novice
Command button. To multi command
 
Join Date: Aug 2020
Posts: 7
Taylormations is on a distinguished road
Default

Gmayor.
I have had a design setback due to opperation errors ask know it all errors haha.


Here is a rundown

Issues
1. People failing to change date/ shift time and save/ sending over previous versions of files.
2. Indexing of files inaccurate.
3. Files sent to client and email title wrong causing confusion and inconvenience.
4. People changing text format and styles.
5. When people cut and paste content into document content controls get changed.
6. Inability to lock file fully to filling form because repeating sections stop working.

Outcome intention
1. Happy a single template master file have. Or auto saving file or duplicating file.
2. Something to detect if file exists and / or prompts user to check shift date/ time on open load or before sending or on document open.

Ideas
Potentoal to have Auto save from single file source. As it's a ongoing updated document from previous shift.
Or prompt if closed to check and save correct date/shift prior being closed.
• ideally auto save
Or
Prompt when sending to check double check date and time in document before sending.



Upper management had requested that I fully get it locked down due to company and client liking it the users are incompetent and attempting to modify document beyond control intentions.


Can you let me know if you can assist as I am at my teacher and something that went well the team messed up because they played with things they don't know about.
I have the file re worked to the best of a template I can.
Can email me on Tyrone @ taylormations . Com. au so I can securely transfer the or cannot direct send on forum? It's changed alot.

Again appreciate your time and knowledge.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command Button herman777 Excel Programming 2 06-02-2019 06:45 AM
Show table list by using chek box command and save document on sharepoint by using command button Zhibek Word VBA 3 08-16-2018 06:19 PM
Help with a Command Button dkohnken Word VBA 5 03-18-2015 10:13 PM
Command Button knp11 PowerPoint 2 01-18-2015 01:41 PM
Command button. To multi command Command Button cksm4 Word VBA 7 02-27-2011 08:47 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft