VBA E-mail (1 Viewer)

W

whhaatt

Registered User.
Local time
Yesterday, 19:08
Joined
Aug 10, 2005
Messages
42
I have been trying for hours to send form output via E-mail.

I have got it working, but the body only outputs 1 line.

Please see code below

Private Sub Logcall_Click()
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error
Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail
'Set body format to HTML
.To = Email_Address
.Cc = "test@test.co.uk"
.Subject = "Call Ref CR000" & (Me.Call_Number) & " > " & (Me.Contract) & " > " & (Me.Call_Title)
.Body = ""
.Body = "System Support SharePoint "
.Body = "You have Successfully Logged a call with the System Support Team. Your Call Reference is :" & Me.Call_Number
.Body = ("The details of the call are as follows : - ") & (Me.Extra_Information)
.Body = ("Contract : -") & (Me.Contract)
.Body = ("Call Title : - ") & (Me.Call_Title)
.Body = (Me.Select1) & " > " & (Me.Select2) & " > " & (Me.Select3) & " > " & (Me.Select4)
.Body = ("Call Details: - ")
.Body = (Me.Extra_Information)


.send
End With

MsgBox "Call Logged Successfuly Your Call Ref Number is " & "CR000" & Me.Call_Number

End Sub
Click to expand...

and the resulting e-mail is as below

Subject: Call Ref CR0003 > Test > Firewall Exception

Body
Please open extra Ports
Click to expand...

The e-mail only outputs the last line in Body from the script, please can anyone help?
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 03:08
Joined
Nov 30, 2011
Messages
8,494
Well I am not sure how you wanted it to be.. but by using .Body = in every single line you are changing it continually..
Code: 
Private Sub Logcall_Click()
    Dim olApp As Object
    Dim objMail As Object

    On Error Resume Next 'Keep going if there is an error
    Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
    Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
        'Set body format to HTML
        .To = Email_Address
        .Cc = "test@test.co.uk"
        .Subject = "Call Ref CR000" & (Me.Call_Number) & " > " & (Me.Contract) & " > " & (Me.Call_Title)
        .Body = "" & vbCrLf & _
                "System Support SharePoint " & vbCrLf & _
                "You have Successfully Logged a call with the System Support Team. Your Call Reference is : " & Me.Call_Number & vbCrLf & _
                "The details of the call are as follows : - ") & (Me.Extra_Information) & vbCrLf & _
                "Contract : -" & Me.Contract & vbCrLf & _
                "Call Title : - " & Me.Call_Title & vbCrLf & _
                Me.Select1 & " > " & Me.Select2 & " > " & Me.Select3 & " > " & Me.Select4 & vbCrLf & _
                "Call Details: - " & vbCrLf & Me.Extra_Information 
        .send
    End With

    MsgBox "Call Logged Successfuly Your Call Ref Number is " & "CR000" & Me.Call_Number
End Sub

I just added vbCrLf.. Hoping you just wanting to Add line feed..
 
W

whhaatt

Registered User.
Local time
Yesterday, 19:08
Joined
Aug 10, 2005
Messages
42
Hi Thanks for that - It dosen't seem to like & vbCrLf & _

Any ideas?
 
W

whhaatt

Registered User.
Local time
Yesterday, 19:08
Joined
Aug 10, 2005
Messages
42
Ok I have got the ouput to come out on the email - with the following code

Private Sub Log_Call_Click()
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Email_Address
.Cc = "test@test.com"
.Subject = "Call Ref CR000" & (Me.Call_Number) & " > " & (Me.Contract) & " > " & (Me.Call_Title)
.htmlBody = "" & vbNewLine & _
"System Support SharePoint " & vbNewLine & _
"You have Successfully Logged a call with the System Support Team. " & vbNewLine & _
"Your Call Reference is : CR000" & Me.Call_Number & vbNewLine & _
"The details of the call are as follows : - " & (Me.Extra_Information) & vbNewLine & _
"Contract : -" & Me.Contract & vbNewLine & _
"Call Title : - " & Me.Call_Title & vbNewLine & _
Me.Select1 & " > " & Me.Select2 & " > " & Me.Select3 & " > " & Me.Select4 & vbNewLine & _
"Call Details: - " & vbNewLine & Me.Extra_Information
.send
End With

MsgBox "Call Logged Successfuly Your Call Ref Number is " & "CR000" & Me.Call_Number
End Sub
Click to expand...

However I cannot get carriage returns, I have tried the following

vbCrLf
vbCr
vbLf
vbNewLine

the output in the e-mail is as follows

System Support SharePoint You have Successfully Logged a call with the System Support Team. Your Call Reference is : 3 The details of the call are as follows : - Please open extra Ports Contract : - Call Title : - Firewall Exception > > > Call Details: - Please open extra Ports
Click to expand...

As you can see not line breaks, I presume that the body of the e-mail is HTML so would I not have toy use HTML parameters for line break, If so how do I incorporate that into the coding?
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 03:08
Joined
Nov 30, 2011
Messages
8,494
What do you mean it does not like vbCrLf? Does it throw any error? I just saw your code and you have a comment saying you are setting the Format to HTML.. If so the code should be.. .HTMLBody.. and you can change your code as,
Code: 
Private Sub Logcall_Click()
    Dim olApp As Object
    Dim objMail As Object

    On Error Resume Next 'Keep going if there is an error
    Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
    Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
        'Set body format to HTML
        .To = Email_Address
        .Cc = "test@test.co.uk"
        .Subject = "Call Ref CR000" & (Me.Call_Number) & " > " & (Me.Contract) & " > " & (Me.Call_Title)
        .HTMLBody = "<br>System Support SharePoint <br>"
                    "<br>You have Successfully Logged a call with the System Support Team. Your Call Reference is : " & Me.Call_Number
                    "<br>The details of the call are as follows : - ") & (Me.Extra_Information)
                    "<br>Contract : -" & Me.Contract
                    "<br>Call Title : - " & Me.Call_Title & " - " &    Me.Select1 & " > " & Me.Select2 & " > " & Me.Select3 & " > " & Me.Select4
                    "<br>Call Details: - <br>" & Me.Extra_Information 
        .send
    End With

    MsgBox "Call Logged Successfuly Your Call Ref Number is " & "CR000" & Me.Call_Number
End Sub
 
W

whhaatt

Registered User.
Local time
Yesterday, 19:08
Joined
Aug 10, 2005
Messages
42
I have managed to get it working - Thankyou

Here is my solution

rivate Sub Log_Call_Click()
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Email_Address
.Cc = "test@test.com"
.Subject = "Call Ref CR000" & (Me.Call_Number) & " > " & (Me.Contract) & " > " & (Me.Call_Title)
.HTMLBody = "<FONT FACE='Arial'><h1><FONT FACE='Arial'>System Support SharePoint</h1><br>" & "<br>You have Successfully Logged a call with the System Support Team.<b> <br><br>Your Call Reference is : <font color='red'>CR000" & Me.Call_Number & "</font></b>" & "<br><b>Requestor Name : </b></br>" & Me.Requestor_Name & "<br><b>Department : </b></br>" & Me.Department & "<br><b>Contact Telephone Number : </b>" & Me.Contact_Telephone_Number & "<b> Ext : </b></br>" & Me.EXT & "<br><b>Email Address : </b></br>" & Me.Email_Address & "<br><b>LGS Contract : </b>" & Contract & "<br><b>Team Manager Name : </b></br>" & Me.Team_Manager_Name & "<br><b>Date Call Logged : </b></br>" & Date_of_Call & "<br><b>Call Title : </b></b>" & Me.Call_Title & "<br><b>Call Allocation : </b>" & Selection1 & "<b> > </b>" & Selection2 & "<b> > </b>" & Selection3 & "<b> > </b>" & Selection4 & "<br><br><b>The details of the call are as follows : </b></b><br>" & (Me.Extra_Information)
.send
End With

MsgBox "Call Logged Successfuly Your Call Ref Number is " & "CR000" & Me.Call_Number
End Sub
Click to expand...

Here is the output

System Support SharePoint


You have Successfully Logged a call with the System Support Team.

Your Call Reference is : CR0003
Requestor Name : Joe
Department : System Support
Contact Telephone Number : 12345678910 Ext : 1111
Email Address : manager@test.com
LGS Contract : Test
Team Manager Name : Joe Bloggs
Date Call Logged :
Call Title : Firewall Exception
Call Allocation : User Credentials > > >

The details of the call are as follows :
Please open extra Ports
Click to expand...
 
You must log in or register to reply here.

Similar threads

K
Solved How to add a signature to a mail
Replies
6
Views
476
KitaYama
K
L
Runtime Error 2147467259 (80004005)
Replies
11
Views
633
The_Doc_Man
The_Doc_Man
S
VBA Code for Bold Text & Hyperlink
Replies
19
Views
1,296
cheekybuddha
cheekybuddha
Sam Summers
sending an email from a form in access 2019 to outlook 365
2
Replies
23
Views
2,198
Sam Summers
Sam Summers
N
Solved Send email from ms access vbs from another email address?
Replies
4
Views
1,124
Gasman
Gasman

Users who are viewing this thread

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