~Code tidy - Email

G

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 14:10
Joined
Nov 8, 2005
Messages
3,309
OK i have this working a treat - but its messy
I click the button and it populates th eemail with informaiton from my form
My form have Text fields on it (I will rename them later) and these text fields are populated via a combo drop down - but it looks a little sloppy from Blockquote onwards
effectively blockquotes is a standard letter 5 paragraphys-
any pointers on tiding up .........



Private Sub EmailerZ_Click()

Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)


EmailSend.to = [Forms]![quotefrm]!
EmailSend.Subject = " Our Reference :-" & " " & [Forms]![quotefrm]![QuoteNo] & " - " & "(" & [Forms]![quotefrm]![Insured] & ")"


EmailSend.HTMLBody = _
"<p>" & "For the Attention of :-" & [Forms]![quotefrm]![POC] & " </p>" _
& vbCrLf & vbCrLf _
& "<br>" & "Insured :-" & [Forms]![quotefrm]![Insured] & " </br>" _
& "<br>" & "Event :-" & [Forms]![quotefrm]![Eventname] & " </br>" _
& "<br>" & "Dates :-" & [Forms]![quotefrm]![openfrom] & " To " & [Forms]![quotefrm]![opento] & " </br>" _
& "<br>" & "Venue :-" & [Forms]![quotefrm]![venuename] & " </br>" _
& "<h1>" & [Forms]![quotefrm]![Text184] & "</h1>" _
& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text151] & "</blockquote>" & "</blockquote>" _
& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text155] & "</blockquote>" & "</blockquote>" _
& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text157] & "</blockquote>" & "</blockquote>" _
& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text277] & "</blockquote>" & "</blockquote>" _
& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text279] & "</blockquote>" & "</blockquote>" _
& "<br>" & [Forms]![quotefrm]![AccounthandName] & "</br>" _
& "<br>" & [Forms]![quotefrm]![Accounthandtitle] & "</br>" _
& "<br>" & [Forms]![quotefrm]![AccEmail] & "</br>" _
& "<br>" & [Forms]![quotefrm]![accTel] & "</br>" _
& "<br>" & [Forms]![quotefrm]![AccFax] & "</br>" _
& "<br>" & [Forms]![quotefrm]![Webaddz] & "</br>" _
& vbCrLf & vbCrLf _
& "<p>" & [Forms]![quotefrm]![AccountHandsignoff1] & "</p>" _
& "<p>" & [Forms]![quotefrm]![accountHandsignoff2] & "</p>" _
& "<font size=""2"">" & [Forms]![quotefrm]![accountHandsignoff8] & "</font>"



If IsNull([attachmentfld]) Then
'do nothing'




Else
EmailSend.Attachments.Add ([attachmentfld])

End If


'Chr(9)

EmailSend.Display

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing
 
I have to go to a meeting but here's some cleaned up

Code: 
Private Sub EmailerZ_Click()
    Dim frm    As Form
    Const bq   As String = "<blockquote>"
    Const bqe  As String = "</blockquote>"

    Dim EmailApp, NameSpace, EmailSend As Object
 
    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.GetNamespace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)
 
    Set frm = Forms!quotefrm
 
    EmailSend.to = [Forms]![quotefrm]![email]

    EmailSend.Subject = " Our Reference :-" & " " & frm![QuoteNo] & " - " & "(" & frm![Insured] & ")"

    EmailSend.HTMLBody = _
    "<p>" & "For the Attention of :-" & [Forms]![quotefrm]![POC] & " </p>" _
                         & vbCrLf & vbCrLf _
                         & "<br>" & "Insured :-" & frm![Insured] & " </br>" _
                         & "<br>" & "Event :-" & frm![Eventname] & " </br>" _
                         & "<br>" & "Dates :-" & frm![openfrom] & " To " & frm![opento] & " </br>" _
                         & "<br>" & "Venue :-" & frm![venuename] & " </br>" _
                         & "<h1>" & frm![Text184] & "</h1>" _
                         & "bq" & "bq" & frm![Text151] & "bqe" & "bqe" _
                         & "bq" & "bq" & frm![Text155] & "bqe" & "bqe" _
                         & "bq" & "bq" & frm![Text157] & "bqe" & "bqe" _
                         & "bq" & "bq" & frm![Text277] & "bqe" & "bqe" _
                         & "bq" & "bq" & frm![Text279] & "bqe" & "bqe" _
                         & "<br>" & frm![AccounthandName] & "</br>" _
                         & "<br>" & frm![Accounthandtitle] & "</br>" _
                         & "<br>" & frm![AccEmail] & "</br>" _
                         & "<br>" & frm![accTel] & "</br>" _
                         & "<br>" & frm![AccFax] & "</br>" _
                         & "<br>" & frm![Webaddz] & "</br>" _
                         & vbCrLf & vbCrLf _
                         & "<p>" & frm![AccountHandsignoff1] & "</p>" _
                         & "<p>" & frm![accountHandsignoff2] & "</p>" _
                         & "<font size=""2"">" & frm![accountHandsignoff8] & "</font>"
 
    If IsNull([attachmentfld]) Then
        'do nothing'
 

    Else
        EmailSend.Attachments.Add ([attachmentfld])
    End If

    'Chr(9)
    EmailSend.Display
 
    Set EmailApp = Nothing
    Set NameSpace = Nothing
    Set EmailSend = Nothing
End Sub
oh, and your NameSpace and EmailApp variables are being declared as VARIANT (is that what you want?).
 
And here's a little more clean up for you based on Bob's code (since he's in a rush):
Code: 
Private Sub EmailerZ_Click()
    Dim frm    As Form
    Const bq   As String = "<blockquote>"
    Const bqe  As String = "</blockquote>"

    Dim EmailApp As Object, NameSpace As Object, EmailSend As Object
 
    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.GetNamespace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)
 
    Set frm = Forms!quotefrm
    
    With frm
        EmailSend.to = ![email]
    
        EmailSend.Subject = " Our Reference :-" & " " & ![QuoteNo] & " - " & "(" & ![Insured] & ")"
    
        EmailSend.HTMLBody = _
        "<p>" & "For the Attention of :-" & ![POC] & " </p>" _
                             & vbCrLf & vbCrLf _
                             & "<br>" & "Insured :-" & ![Insured] & " </br>" _
                             & "<br>" & "Event :-" & ![Eventname] & " </br>" _
                             & "<br>" & "Dates :-" & ![openfrom] & " To " & ![opento] & " </br>" _
                             & "<br>" & "Venue :-" & ![venuename] & " </br>" _
                             & "<h1>" & ![Text184] & "</h1>" _
                             & "bq" & "bq" & ![Text151] & "bqe" & "bqe" _
                             & "bq" & "bq" & ![Text155] & "bqe" & "bqe" _
                             & "bq" & "bq" & ![Text157] & "bqe" & "bqe" _
                             & "bq" & "bq" & ![Text277] & "bqe" & "bqe" _
                             & "bq" & "bq" & ![Text279] & "bqe" & "bqe" _
                             & "<br>" & ![AccounthandName] & "</br>" _
                             & "<br>" & ![Accounthandtitle] & "</br>" _
                             & "<br>" & ![AccEmail] & "</br>" _
                             & "<br>" & ![accTel] & "</br>" _
                             & "<br>" & ![AccFax] & "</br>" _
                             & "<br>" & ![Webaddz] & "</br>" _
                             & vbCrLf & vbCrLf _
                             & "<p>" & ![AccountHandsignoff1] & "</p>" _
                             & "<p>" & ![accountHandsignoff2] & "</p>" _
                             & "<font size=""2"">" & ![accountHandsignoff8] & "</font>"
    End With
    
    If Len([COLOR=Red]Me.[/COLOR][attachmentfld] & vbNullString) <> 0 Then
        EmailSend.Attachments.Add ([COLOR=Red]Me.[/COLOR][attachmentfld])
    End If

    EmailSend.Display
 
    Set EmailApp = Nothing
    Set NameSpace = Nothing
    Set EmailSend = Nothing
End Sub
 
Well both version s look a damn site better than I had - mine was more bash it through no finnese?
I haven't tried it yet

so please excuse my lateness
 
tidier - but missing 1 small element carrage returns

& "bq" & "bq" & ![Text151] & "bqe" & "bqe" _* here
& "bq" & "bq" & ![Text155] & "bqe" & "bqe" _* here
& "bq" & "bq" & ![Text157] & "bqe" & "bqe" _*here
& "bq" & "bq" & ![Text277] & "bqe" & "bqe" _*here
& "bq" & "bq" & ![Text279] & "bqe" & "bqe" _*here
 
Ah the orgainl mess after each blockquote it does a carrage return
 
Ok, I missed that and Bob mistakenly put bqe instead of bq. Basically, look at the first 2 lines of your code. bq is a constant representing "<blockquote>" and bqe is the closing tag.

So replace the firtst bqe with bq and you'll be done.
 
Huh....

"bq" & "bq" & ![Text151] & "bqe" & "bqe"

not seeing it ?
 
vbaInet said:
Ok, I missed that and Bob mistakenly put bqe instead of bq. Basically, look at the first 2 lines of your code. bq is a constant representing "<blockquote>" and bqe is the closing tag.

So replace the firtst bqe with bq and you'll be done.
Click to expand...

I did a find and replace so it replaced what was there with bq for the beginning and bqe for the ending exactly as the original code had. I just took a look again and it doesn't appear that I messed up in any way.
 
Ignore that!!

So was there a carriage return in your original code?
 
No there was no need ..

& "<blockquote>" & "<blockquote>" & [Forms]![quotefrm]![Text151] & "</blockquote>" & "</blockquote>"

forced a new line

so the text in text 151 had a break between the one above/below
as it is in the tidy version text 151 is followed straight away by text 155
its like a rambling mess

so 151 = hello how are today
155 = Our special today is ..

(as an example - the actual text is legal stuff)
 
sample of how it worked on the messy version

For the Attention of :-Gary


Insured :-whizzy

Event :-as declared

Dates :-01/01/2001 To 01/01/2001

Venue :-Various Locations



Further to receipt of the completed proposal form, we have prepared the attached quotation based on the information contained therein. We believe that the quotation meets with your client’s demands and needs, unless otherwise stated in this letter.

Together with our quotation, please find enclosed the Key Facts document. It is important that all the attached documentation is read carefully; especially any endorsements that may have been added in the quotation document.

If the quotation does not meet your client’s requirements in any way, or if you have any queries or require further information about the cover provided therein, please do not hesitate to contact me. I look forward to receiving a response from you in d
 
boblarson said:
I did a find and replace so it replaced what was there with bq for the beginning and bqe for the ending exactly as the original code had. I just took a look again and it doesn't appear that I messed up in any way.
Click to expand...
Nope, you didn't. I just took a quick glance :o
 
this is what I am now getting (i like the top half -its the bottom Text area thats gone wobbley)
(And sorry Bob - your code did what it menat to - but the blockquote forces a carrage return - I should of pointed this out - the rest of it worked a charm <
is there someway of adding chr13?

For the Attention of :-Gary


Insured :-whizzy
Event :-as declared
Dates :-01/01/2001 To 01/01/2001
Venue :-Various Locations



Further to receipt of the completed proposal form, we have prepared the attached quotation based on the information contained therein. We believe that the quotation meets with your client’s demands and needs, unless otherwise stated in this letter.Together with our quotation, please find enclosed the Key Facts document. It is important that all the attached documentation is read carefully; especially any endorsements that may have been added in the quotation document.If the quotation does not meet your client’s requirements in any way, or if you have any queries or require further information about the cover provided therein, please do not hesitate to contact me. I look forward to receiving a response from you in d
 
Actually, there is a problem.

bq and bqe should not be surrounded in quotes. They are not being seen as variables.
 
Result ......
Thanks Bob -Vbainet -
result really sweet - I may need to tweak bits but this certainly tidies it up

Gary
 
vbaInet said:
Actually, there is a problem.

bq and bqe should not be surrounded in quotes. They are not being seen as variables.
Click to expand...

Ah, yes that would be true. I did forget to include the quotes in the replace.
 
boblarson said:
Ah, yes that would be true. I did forget to include the quotes in the replace.
Click to expand...
Find & Replace catches me out sometimes too. Like sometimes when I want to replace a TableName with zls in an SQL statement I forget to include the dot after the TableName in the Find box so I end up deleting the entire table name :D
 
resolved -- big ups to both of you
Cheers

G:D
 
You must log in or register to reply here.

Similar threads

G
windows 7 to 10 - runtime error
Replies
2
Views
887
GaryPanic
G
W
Report in EMail Body
2
Replies
20
Views
2,447
Gasman
Gasman
G
code tidy (part 2)
Replies
5
Views
984
vbaInet
V
G
HTML - Code (into Ouitlook)
Replies
1
Views
857
HiTechCoach
HiTechCoach
G
Access to Outlook (Body of the email) info
Replies
0
Views
787
GaryPanic
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom