Re: VBA Email Format Problem
That has worked perfectly!
Re: VBA Email Format Problem
That has worked perfectly!
I am working on a document that takes data from A userform field, converts it to HTML and sends the information in an email. I've got everything working really well using lots of help from this website and Ron deBruin's Email code.
The only niggly problem I have is if a particular field in the userform is left blank, then the email generated still has a blank line and I really want the line to be deleted and the lines below moved up one. An example is here:
25 Any Street
What I really want is:
25 Any Street
I've tried putting in a few IF statements to try and get round it but it keeps putting the blank line in because the only code I know how get a blank character is "". This obviously treats the line as blank but still a separate line. Here is the code I have so far.
StrBody = "<font size=""11px"" face=""Segoe UI"" color=""black"">" & "<br>" & _ "Dear " & frmCalc.txtSP1.Value & SP2 & SP3 & "," & "<br><br>" & _ " Some text " & frmCalc.DTPDate.Value & "." & "<br><br>" & _ 'this is the beginning of the address code "<b>Customer:</b><br>" & frmCalc.txtCust.Value & "<br><br>" & _ 'Customer in Bold & Customer Name from Form "<b>Address:</b><br>" & _ 'Address in Bold txtAdd1.Value & "<br>" & _ 'Address Line 1 from Form txtAdd2.Value & "<br>" & _ 'Address Line 2 from Form txtCity.Value & "<br>" & _ 'Address Line 3 from Form txtCounty.Value & "<br>" & _ 'Address Line 4 from Form frmCalc.txtPC1.Value & " " & frmCalc.txtPC2.Value & "<br>" 'Post Code from form
I can provide some more supporting code but I'm fairly sure this is where the changes need to be made.
Can anyone suggest a possible solution? This would sort out the same issue I have with a couple of other project I have as well so any helpful code would be most appreciated.
Many Thanks as always
Re: Send Data from Userform to Worksheet AND Send Userform fields in Email
I have now solved this problem so no longer require any assistance. If anyone is interested, it was all down to the order of the code. The code to send the email had to be run before the code to save to the worksheet.
I have used a database template from this site and changed it to suit my needs but I have a bit of a problem with some of the code. I know how to update the worksheet with the relevant userform text fields and in another project I did I have successfully sent userform text fields in the body of an email.
For this project I want to update the worksheet AND send an email at the same time. However, using the two pieces of code together is causing an error that I can't seem to solve (using my very limited vba knowledge!). The code I am working on is below and I have highlighted the line that is getting the error message. Can anyone help with a solution?
Thanks in advance!
Private Sub cmdSubmit_Click() 'Submit new record Dim ws As Worksheet, lRow As Long, Str As String Set ws = Sheets("Database") '<=== ERROR STARTS ON THIS LINE lRow = ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Row '____error handling______ If txtAccNo.Value = "" Or Nullstring Then MsgBox "Please enter an Account Number", vbCritical, "Error" Me.txtAccNo.SetFocus GoTo error1 End If If txtAccName.Value = "" Or Nullstring Then MsgBox "Please enter an Account Name", vbCritical, "Error" Me.txtAccName.SetFocus GoTo error1 End If If txtPost.Value = "" Or Nullstring Then MsgBox "Please enter a Post Code", vbCritical, "Error" Me.txtPost.SetFocus GoTo error1 End If If txtCust.Value = "" Or Nullstring Then MsgBox "Please enter a Customer Name", vbCritical, "Error" Me.txtCust.SetFocus GoTo error1 End If If txtComp.Value = "" Or Nullstring Then MsgBox "Please enter a Complaint Type", vbCritical, "Error" Me.txtComp.SetFocus GoTo error1 End If If txtDetail.Value = "" Or Nullstring Then MsgBox "Please enter the Complaint Details", vbCritical, "Error" Me.txtDetail.SetFocus GoTo error1 End If '____error handling end______ If MsgBox("You are about to add this Record." & vbCr & "Do you wish to continue?", vbYesNo, _ "Confirm Add") = vbYes Then ws.Cells(lRow, "A") = txtRef.Value ws.Cells(lRow, "B") = CDate(txtDate.Value) ws.Cells(lRow, "C") = txtOrig.Value ws.Cells(lRow, "D") = txtAccNo.Value ws.Cells(lRow, "E") = txtAccName.Value ws.Cells(lRow, "F") = txtPost.Value ws.Cells(lRow, "G") = txtCust.Value ws.Cells(lRow, "H") = txtComp.Value ws.Cells(lRow, "I") = txtDetail.Value ws.Cells(lRow, "J") = txtAction.Value Dim OutApp As Object Dim OutMail As Object Dim strBody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strBody = "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate & vbNewLine & _ "Date Raised: " & frmComp.txtDate On Error Resume Next With OutMail .To = "email address" '<= To Change '.CC = "team leader & originator" .Subject = "Subject" .body = strBody .Display '.Send .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End If Unload Me MsgBox ("Record Saved") error1: End Sub
Has anyone got some example code to populate userform text fields from multiple sheets depending on which sheet has been selected?
I have a workbook with recipes on multiple sheets. The row and column data on each sheet are in the same location (i.e ingredient 1 = A1, ingredient 2 = A2, etc).
What I want to do is have a userform that pulls the data from the sheet when it is initialized, but the sheet is variable depending on which sheet the user wants to view.
For example: The user wants to view Recipe 1 which is on sheet1. When they click the Command Button for Recipe 1 the Userform opens with Sheet1 data. If they want to view Recipe 8, the userform opens with Sheet8 data.
I've set a cell to populate the sheet reference and then used this cell as a variable in the code but the sheet comes up blank. I'm in the VERY early stages of this project so haven't really got any code that is worth posting because this problem has stumped me already!
Hope you can help. Any example code will be most useful.
Re: Copy Dynamic Range from Master Workbook to Archive Workbook
Hi Luke M
This works perfectly! I had the same idea of opening both workbooks but I was getting tied up keeping track of them once they were open.
Thanks for your help
I have been using this resource for a while and have always found it incredibly useful. The problem I have is very easily solved I'm sure, but I just can't seem to work it out. I have a master workbook that holds rows of data sent from a Userform. Once the data has been analysed it needs to be Archived periodically. The Achive file is in another location. Now I have managed to adapt the below code to copy the rows to another sheet in the same workbook but can't seem to adapt the code to move it to the other workbook. I am sure it's just a case of changing the destination file but so far it has me stumped.
If anyone can offer an alternative code or an amendment to mine then it will be much appreciated.
Sub cmdArchive() Dim lngLastRow As Long, lngPasteRow As Long Application.ScreenUpdating = False lngLastRow = Sheets("tblRecord").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row If WorksheetFunction.CountA(Sheets("tblTest").Cells) = 0 Then lngPasteRow = 1 Else lngPasteRow = Sheets("tblTest").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 End If Sheets("tblRecord").Range("A1:G" & lngLastRow).Copy _ Destination:=Sheets("tblTest").Range("A" & lngPasteRow) Application.ScreenUpdating = True With Sheets("tblRecord") .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear End With End Sub
This code copies the rows to the "tblTest" worksheet but how do I get it to copy to the "Archive Workbook" instead. For the purpose of an example, the "Archive Workbook" is saved in c:\Excel\Archive
Re: Return Worksheet Row Data that is User Specific
That sounds like the sort of thing I am looking for. I haven't used a ListBox yet, but will that filter data based on the value in the ListBox? If so, I should be able to write that code once I have swatted up on ListBoxes.
I am fairly new to VB but have really started to get into it as I find it invaluable in my job. I have been searching Google for the answer to my query, but so far have drawn a blank.
I have a Worksheet that has rows of data that have been added using a Userform. I then have another Userform to review this data. I have cobbled together the code from various sources so that when the userform is opened, the data populates and the user can then navigate the the next line or previous line. This is great, but I don't want all of the users to see all of the data. I only want them to be able to see the data that they have entered.
I suspect the solution will involve an InputBox that asks for the user name and the result of that InputBox will trigger the userform to return data specific to that user. The data isn't sensitive, so if a user chose another persons name, it would not be a problem that they can see that persons data. I just want to make it easy for the user to see their own records.
I can attach my worksheet if that will help, but will have to 'cleanse' it first. However, if anyone has ready written code for this, I'm sure I will be able to slot it in myself.
Just want to say what a great source of information this forum is. I would be nowhere without it!!!