use data from a User Form to email a specific person

Hi
After re thinking and with some redesign ideas I have created a User Form that I want to send to my Providers . I have attached the form . I have manged to get the show User form function , search, clear , clear form and combo boxes to work.
I need help with a couple of things :

1. to make the search function non case sensitive if possible
2. get an msg box if the provider ID is not fond.

Also I haven't been able to nut out is how to email the user form with its data / info to a specific provider based on the search. The Search finds the Provider ID , Persons name and email. I have seen code before where the user form with the the information has been converted to PDF format and email(textbox 2)provider ID I am hoping that someone can provide me assistance

The email needs to have some design standardization :
1. it will be sent from one email address
2. the subject line need to be "GPU e Journaling
3. the To field needs be the provider ID as based on the search button in the user form
4. body of email should have something like "Dear student we have identified some errors in your charting . Please see attached doc and rectify the errors as soon as possible yours sincerely XXXXXX
Once sent it would also be nice to have a conformation msg box "message sent

I have hunted and havent found anything that I can adapt which is in part due to my lack of experience. until last week I hadn't even done any VBA at all . Hoping some one can wrote the code ? : pray2:

Welcome to the forum! You have requested several things in one post. As such, some will not respond as they don't know how or don't want to spend a lot of effort addressing all of the requests.

For the first (1): A simple Range find will suffice.
for the first (2): If Range found is Nothing, then no data would be added. Instead, I would make the textbox into a combobox so there will always be a found ID. I like to make the ID items unique and sorted for the combobox list.

For the userform, one can do it via a pdf or use Outlook's WordEditor method to paste a copy into the body of the email.


For the 2nd list items:

1. It will always be sent by the logged on user. If multiple accounts, an account can be set. If not logged on, one can force a logon. Ron de Bruin has example code for that. https://www.rondebruin.nl/win/s1/outlook/openclose.htm

If not logged on, a Sent mail is not sent from the outbox until a logon. One can add a .SentOnBehalfOf.

2 and 3 are trivial.4 is trivial too unless you wanted to paste the userform pic into the body. It just takes different code to do that. It is probably more efficient than the pdf create and attach route. e.g. http://www.vbaexpress.com/forum/show...Email-userform

If I get time tonight and you still need help, I can put most of this into a code example using your file.

This should get you close. After the Dim lines, modify variable values to suit.

The email button makes a BMP file and attaches it to an email.

It it were me, I would sort the data sheet. I used a two column pick list as IDs alone are not always helpful to me.

Comment out .Display and uncomment .Send when it works as needed.

Thanks Kenneth. I see that you have cleaned up and consolidated the code . It works perfectly thank you so much ,Ill review and get my head around the code and test. Ill also take it to work and see if the users can break it !!!!
At the risk of pushing the limit and because I really dont know ...the provider field ( search ID) does it have to be a drop down only cause I will have 200 plus IDs ??? If It cant I get why you said sort it first . Users will want the easiest option .

Hey Kenneth me again ... forget what I posted on further looking it works brilliantly . Combo box you can type in and gives the result .. Apologies for my ignorance:thumb:thumb
I will play and learn and take to work place to see if it will work on PC's . Our PCs are locked down pretty hard so anything can happen.
I cant thank you enough you do deserve the Title Guru

Attachment 24787

Hi again Kenneth . made a few tweaks to the form but your email code works well.
Is there any chance at all to get from the combo box 4 (drop down provider) , get the Provider ID and the email address closer together .It would assist the Users in getting the correct recipient I have added an image to help with the request . here's hoping

Flowers are nice, thanks! You do well in showing what you have and what you want. That helps others help better.

One can set the control's ColumnWidths property at design time or in code. Here is the 2nd method.

Code:

---

Private Sub UserForm_Initialize() 
  Dim ws As Worksheet
 
  Set ws = Worksheets("Data")


  With ComboBox1
      .AddItem ""
      .AddItem "New Med History required as new CoC "
      .AddItem "Consent associated with treatment plan current, Fee estimate given"
      .AddItem "Written consent required eg for crown bridge work,_Full clearnaces and treatment codes and services"
      .AddItem "Treatment plan completed and service  codes in chart match the plan"
      .AddItem "OPG referral - 037 Viewed, 037 Report codes completed"
      .AddItem "Referral to Specialist clinic (019) - ADH92 form completed and signed by tutor"
      .AddItem "Is this last treatment? If so is there TREAT_COMPLETE service code?"
    End With
    ComboBox2.List = ComboBox1.List
    ComboBox3.List = ComboBox1.List
    With ComboBox4
      .ColumnCount = 2
      .RowSource = ws.Range("A1", ws.Range("A1") _
        .End(xlDown)).Resize(, 2).Address(external:=True)
      'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/columnwidths-property?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(fm20.chm2000960)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue
      .ColumnWidths = "0.5 in; 2 in"
    End With
End Sub

---

If you have time, you might consider a more robust sort of "form" to email. In database terms, your Data worksheet is a Table. You set that up nicely. Another worksheet can be setup like a Form in database terminology. It shows values from the Table. This gives you more power and exporting it to pdf is trivial. The email part is similar to post # 3 but even more simple. A combobox or listbox control would be similar to the userform's combobox control's code. Its job is to assist picking the unique ID value. In database terms, that would be the Primary Field (column in worksheet like ID). Once that is set, all the other Fields, cells in the worksheet, are filled based on a vlookup() formula.

Other nice features for the method above is that if you want to mass/batch make PDFs and/or emails, it is easy. e.g. Class GPU 101 students all need a reminder email. One uses a filter in a macro to add the ID for each row in worksheet Data that has a column value of "GPU 101".

I have attached an example that uses the concepts detailed above in case it interests you or others. It does not use API code as in file in #3. API code can fail if not coded to handle both 32 bit and 64 bit Excel.

Hi Ken
Thanks for the prompt reply ... I try to give as much info as I can . Busy weekend so will have a look at this tomorrow to get a better and fuller understanding .. many thanks for your suggestions and educating me .
This is the best I can do a bouquet of Tim Tams .... flowers a bit hard for me to pull off Attachment 24793

I am not sure why your file size is large.

1. Try saving file as .xlsb.
2. Search for: Excel File Bloat
3. Use fewer objects in your file.

Method (3) can bloat a file when one adds and deletes many pics/objects from the clipboard to your worksheet. In the sendkeys example file that I attach here, it pastes the copied screen snippet to a scratch workbook. I avoid sendkeys solutions whenever possible. Success with those is all about focus and timing. While the API code is shorter, shorter is not always best. Many like this method though.

The 2nd attachment here uses API code too. Its code is more similar to the post #3 method. It is more robust and pastes the copied userform to a sheet and creates a pdf file. It can be easily modified to paste to a scratch workbook to make the pdf file as the other does. It better avoids file bloat and corruption issues.

As you may know, it is easy to use a whole module in one workbook that another uses. In the VBE, simply drag and drop the modules from one workbook to another from Project Explorer. If not open, select it from the VBE View menu.

These two files are good ones to put in your back pocket.

Hi again
Well the code you supplied did take a" snap" shot of the form to a scratch work book ... Locked it up not sure where to go form there to get it into an email.
if it were to work the steps would have to be similar to:
1. User form search
2.command button 3 email form
code to get scratch image to worksheet. attach as PDF --> email
scratch image would need to be deleted

I have been searching and found code below ... is this appropriate to send the user form as a a small PDF .
Your advice appreciated

Code:

---

Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = Sheet1.GPUEjournalling
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & Sheet1.GPUejournalling & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
 
    ' Prepare e-mail
    .Subject = Title
    .To = "Textbox13" ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
 
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
 
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
 
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

---

Run it and see. For testing purposes, change the .Send to .Display.

I don't know what userform search means. The usual practice is to run the userform capture from the userform triggered by a command button. Maybe you meant the range find (search) used in post #3 file.

Did you Compile the code before running it? Compile is in VBE's Debug menu. Immediate window can be shown from the View menu if you don't have it displayed.

I doubt that this does anything.

Code:

---

Sheet1.GPUEjournalling

---

After a run, if it even runs without error, what does the Immediate window show after adding this and a Run?

Code:

---

PdfFile = PdfFile & "_" & Sheet1.GPUejournalling & ".pdf"
Debug.PRint PdfFile

---

Nowhere does that code do a screen capture and paste it to a worksheet. Other than those lines, it would create a pdf of the active worksheet and email it.

Everything that I posted works for me. If you need help debugging a file, attach it after scrubbing sensitive information. The one that I did in post #3 can be changed to make the pdf instead of a bmp using the Copy Userform file's methods in post #9. Of course post #7 file is the method that I would do for a pseudo mail merge.

HI Ron
I did run it through debugger but got no where .I may be using the incorrect terminology by üserform ( the one designed I mean the form that I created with VBA.see image)
Had one thought to get the size down. Is it possible to to get the data form the userform data into a hidden sheet , send that range and then send that via email as a PDF ,after sending have the hidden sheet deleted ?
It seems that all the solutions previously in forum have had the data sent from a spreadsheet not the form itself .
Attachment 24801
Could you use the code in Post three to adapt as I havent changed and it seems that my log in wont allow attachments

The userform should be resized. This makes a pdf.

Hi Kenneth
First off sorry for calling you "Ron" it never pays to reply to at 3.30AM !
Secondly "Hazza !"I think your work here is done . I have spent several hours testing at work to see if it works , and it does !
I made a few changes like adding another search criteria / fields and changed some of the email template , simple stuff to you but a good lesson for me
Just going to give it to users to see if they can follow the process without breaking it . I am confident that it will be. The file size is right down to 43kb .
It"s magic pure magic. Thank you so much for the email section , there is no way I could have got this done on my own. Still looking at it so I can fully understand.

Happy for this file to be made available to others all credit to you Sir ! Hopefully it may help others who have the same issue .
Thank you again for your patience , knowledge and willingness to help .

Some Aussies may say "Mate you are the ducks nuts"no idea where it comes from , just trust that it is a huge compliment !
Cheers

Just an update before I mark this are "solved ". This form has been in use for 2 mths now with about 12 people using it on a daily basis .For the business it has stream lined a once laborious process , increased productivity and increased accuracy . I have not one negative comment .......So thank you to all that viewed and replied and assisted in the final product