Looping through textboxes to copy files and send emails. [Archive]

ecalid

09-18-2023, 06:47 AM

Good afternoon all,

I am wondering if you somebody could help me with a slight issue I have.

I have put together a 'database updater' and this has worked great, unfortunately it can only send a single file to a single folder.

I would like this to be able to cycle through approx 12 textboxes in a userform and then create a folder based on this information, and then send an email to email addressed found in more text boxes.

I will put up my existing code so you can see what I mean:

Ignore the sheet references as the information it will pull will be from userform textboxes instead.

Public Sub sbCopyFile2()
If Range("D5").Value = "Please click the folder icon to select a file to upload." Then
MsgBox "Please select a file to upload by clicking the orange folder icon."
Exit Sub
End If
If Range("D10").Value = "" Then
MsgBox "Please enter a first name."
Exit Sub
End If
If Range("D11").Value = "" Then
MsgBox "Please enter a surname."
Exit Sub
End If
If Range("D12").Value = "Please select department." Then
MsgBox "Please select a department."
Exit Sub
End If
If Range("D13").Value = "Please select a shift." Then
MsgBox "Please select a department."
Exit Sub
End If
If Range("D18").Value = "" Then
MsgBox "Please enter an area"
Exit Sub
End If
If Range("D19").Value = "" Then
MsgBox "Please enter Document Type"
Exit Sub
End If
Dim Cell As Range
With ActiveSheet
For Each Cell In Application.Intersect(.UsedRange, .Range("D10:D11")).Cells
Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Mid(Cell.Text, 2))
Next Cell
End With
Sheet5.Range("D10").Value = Trim(Sheet5.Range("D10"))
Sheet5.Range("D11").Value = Trim(Sheet5.Range("D11"))
Sheet5.Range("D19").Value = Trim(Sheet5.Range("D19"))
Sheet5.Range("D20").Value = Trim(Sheet5.Range("D20"))
Dim trg2 As String, src As String
Dim ok As Boolean
Dim OutApp As Object
trg2 = Sheet5.Range("D36") & ""
src = Sheet5.Range("filePath") & ""
If Len(src) <> 0 Then
If Len(Dir$(src)) <> 0 Then
ok = True
End If
Else
MsgBox "Please select a file to upload."
Exit Sub
End If
If Not ok Then
MsgBox "Source document does not exists."
Exit Sub
End If
SpecialMkDir (trg2)
If Len(Dir$(trg2)) <> 0 Then
MsgBox ("This document already exists. Please choose another name or date for the document.")
Exit Sub
End If
VBA.FileCopy src, trg2
Dim OutMail As Object
Dim strbody As String
Dim MakeJPG As String
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
eRecipient3 = ActiveSheet.Range("G39").Value
strbody = "This is an automated email."
MakeJPG = CopyRangeToJPG("Employee Record Updater", "A1:J27")
If MakeJPG = "" Then
MsgBox "Something went wrong, the email couldn't be created"
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Exit Sub
End If
On Error Resume Next
With OutMail
.To = "XXXXXXXXXXXXXX"
.CC = ""
.BCC = ""
.Subject = "New Employee Record " & "(" & Range("D18") & ")"
.Attachments.Add Range("D36").Value
.Attachments.Add MakeJPG, 1, 1
.HTMLBody = "This is an automated email. A new document has been uploaded." _
& " " & "Employee: " & Range("D10").Value & " " & Range("D11").Value & " " & " _
Department: " & Range("D12").Value & " " & "Shift: " & Range("D13").Value & " _
 " & "File: " & Range("D23").Value & " If you wish to upload a document to an _
employee's file then please use the uploader: <a _
href="XXXXXXXXXXXXXXXXXXXXX">XXXXXXXXXXXXXXXXXXXXXXXXXX</a> Many thanks,"
.Send
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
MsgBox "Thank you. The document has been submitted to H&S. You will recieve a confirmation email shortly."
Resume Next
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = XXXXXXXXXXXXXXXXXX
.CC = ""
.BCC = ""
.Subject = "New Employee Record " & "(" & Range("D18") & ")"
.Attachments.Add Range("D36").Value
.HTMLBody = "This is an automated email. A new document has been uploaded." _
& " " & "Employee: " & Range("D10").Value & " " & Range("D11").Value & " " & " _
Department: " & Range("D12").Value & " " & "Shift: " & Range("D13").Value & " _
 " & "File: " & Range("D23").Value & " If you wish to upload a document to an _
employee's file then please use the uploader: <a href=""XXXXXXXXXXXX"">XXXXXXXXXXXXXXXXXXXXX</a> _
 Many thanks,"
.Send
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Resume Next
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ActiveSheet.Range("D14").Value
.Subject = "New record in your H&S file"
.Attachments.Add Range("D36").Value
.HTMLBody = "This is an automated email and your email address was not saved. _
 Hello, " & Range("D10").Value & " " & "A new document has been uploaded to your H&S file. Please see _
attached." & " " & "Employee: " & Range("D10").Value & " " & Range("D11").Value & " _
 " & "Department: " & Range("D12").Value & " " & "Shift: " & Range("D13").Value & " _
 " & "File: " & Range("D23").Value & " Many thanks,"
.DeleteAfterSubmit = True
.Send
End With
On Error GoTo 0
Kill MakeJPG
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
If Range("D14").Value > 0 Then
MsgBox "A copy was sent to the employee's email provided."
Else
End If
Range("D5").Value = "Please click the folder icon to select a file to upload."
Exit Sub
End Sub

Function CopyRangeToJPG(NameWorksheet3 As String, RangeAddress As String) As String
Dim PictureRange As Range
With ActiveWorkbook
On Error Resume Next
.Worksheets(NameWorksheet3).Activate
Set PictureRange = .Worksheets(NameWorksheet3).Range(RangeAddress)
If PictureRange Is Nothing Then
MsgBox "Sorry this is not a correct range"
On Error GoTo 0
Exit Function
End If
PictureRange.CopyPicture
With .Worksheets(NameWorksheet3).ChartObjects.Add(PictureRange.Left, PictureRange.Top, PictureRange.Width, _
PictureRange.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & Application.PathSeparator & "newempdoc.jpg", "JPG"
End With
.Worksheets(NameWorksheet3).ChartObjects(.Worksheets(NameWorksheet3).ChartO bjects.Count).Delete
End With
CopyRangeToJPG = Environ$("temp") & Application.PathSeparator & "newempdoc.jpg"
Set PictureRange = Nothing
End Function

Private Sub SpecialMkDir(ByVal path As String)
Dim var As Variant, p As String
Dim i As Integer
var = Split(path, "")
On Error Resume Next
For i = 0 To UBound(var) - 1
p = p & var(i)
VBA.MkDir p
p = p & ""
Next
End Sub

31051

Any help you could provide would be amazing.

Many thanks in advance.

June7

09-18-2023, 07:57 AM

Should post code between CODE tags, not QUOTE tags.

Could also provide Excel file for analysis.

What database is this updating? Why use Excel as GUI?

Paul_Hossler

09-18-2023, 10:31 AM

I updated OP to use CODE tags. QUOTE tags have potential to include unwanted formatting tags, etc.

Aussiebear

09-18-2023, 01:23 PM

Is this a typo...?

If Range("D14").Value > 0 Then
MsgBox "A copy was sent to the employee's email provided."
Else
End If
Range("D5").Value = "Please click the folder icon to select a file to upload."

June7

09-18-2023, 01:58 PM

Does this help https://www.thespreadsheetguru.com/loop-specific-userform-control/

Also, if textboxes are named with sequential suffix, like tbxD1, tbxD2, etc, can probably just loop those controls - this works in Access VBA.

For x = 1 to 6
MsgBox = UserForm1.Controls("tbxD" & x).Value
Next

Paul_Hossler

09-18-2023, 05:33 PM

I will put up my existing code so you can see what I mean:

Ignore the sheet references as the information it will pull will be from userform textboxes instead.

Might be easier to help if you updated the code for one textbox and also attached a workbook with a small database sample and the userform etc.