
KjBox OzMVP - Super Moderator
- Male
- from Kuching, Borneo
- Member since Jan 10th 2007
- Last Activity:
- Posts
- 4,873
- Reactions Received
- 243
- Points
- 24,923
- Profile Hits
- 9,456
-
-
Hi KjBox,
Hope you are doing great, need you help on:
- Problem:
- I have to select name one by one and click on email now button so email get generate.
- while converting sheet as PDF, all the columns in not fitting in one PDF page , spltting in 2 pages and row height is getting messed up.
Code
Display MoreSub searchandcopy() Dim datasheet As Worksheet Dim reportsheet As Worksheet Dim accountnum As String Dim finalrow As Integer Dim i As Integer Dim edress As String Dim subj As String Dim message As String Dim filename As String Dim outlookapp As Object Dim erange As Range Dim outlookmailitem As Object Dim myAttachments As Object Dim path As String Dim attachment As String Set erange = Sheet3.Range("A2:B500") Set datasheet = Sheet1 Set reportsheet = Sheet2 accountnum = reportsheet.Range("A1").Value edress = Application.WorksheetFunction.VLookup(accountnum, erange, 2, False) reportsheet.Range("B1").Value = edress reportsheet.Range("A3:I10000").ClearContents datasheet.Select finalrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 4 To finalrow If Cells(i, 1) = accountnum Then Range(Cells(i, 3), Cells(i, 9)).Copy reportsheet.Select Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats datasheet.Select End If Next i reportsheet.Select Set outlookapp = CreateObject("Outlook.Application") Set outlookmailitem = outlookapp.createitem(0) Set myAttachments = outlookmailitem.Attachments 'path = "C:\Users\PradeepPranav\Downloads\" Application.DisplayAlerts = False filename = accountnum & ".pdf" subj = accountnum ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _ path + filename, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=False attachment = path + filename outlookmailitem.To = edress outlookmailitem.cc = "[email protected]" outlookmailitem.bcc = "" outlookmailitem.Subject = subj outlookmailitem.body = "Please find your feedback recieved from banker throung Worflow Echange" & vbCrLf & "Best Regards" myAttachments.Add (attachment) outlookmailitem.display 'outlookmailitem.send Application.DisplayAlerts = True Set outlookapp = Nothing Set outlookmailitem = Nothing Range("A1").Select End Sub
-
Can I share the file here where just one line of code needs editing. ?
-
Hi Kj/box. Are you online ?
-
Hi KbBox , how are you ,,,, need your help to solve a small junk
-
Hello, I hope all is good for you. You have helped so much in the past in the hire help section, I have another post there. Are you able accept it? I hope so, Please let me know
Steve
-
Hi are you there?
-
I am now
-
Need your help to solve this
Extract data from a database file- Rmrekoj
- Thursday, 7:20 pm
- Thread is Unresolved
-
-
hi buddy, need a help
-
Hi.
I am just joined the forum. Can you kindly let me know how to start a thread about a problem I have?
Thanks
-
Thanks, I've managed
-
-
Just needing to know how to submit a new post???
-
This mate is a Wizard with the lines!!!
-
Hi I trust you and your Family are keeping well
I tried to send something on Whats app but it needed a code from you now.
-
hello Charles, hope you are well.
-
KjBox is excellent......has helped me so much with VBA codes. A real expert. Thanks for everything.
-
Hi,
in this code, i would like the data to paste in Values Format .this code copy formulas on destination sheet. i want to copy only values not formula. Could you please help me on this.
Thanks in advance
Sub CopySelectedRows()
Dim rCopy As Range
Dim lAreas As Long
On Error Resume Next
Set rCopy = Application.InputBox("Select Rows to copy. " _
& "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , ,
On Error GoTo 0
If rCopy Is Nothing Then Exit Sub 'Hit Cancel
If rCopy.Columns.Count <> Columns.Count Then
MsgBox "Please select entire row(s)"
Run "CopySelectedRows"
Else
For lAreas = 1 To rCopy.Areas.Count
rCopy.Areas(lAreas).Copy Sheet18.Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next lAreas
End If
-
I had no idea about this game, and RE is my favorite series. I want to play through classic RE1 again soon and all these details brought back major flashbacks.
-
Please post your question in the Excel/VBA Forum, ideally with your workbook attached and a sheet added that shows your desired result.
-
Rmrekoj
Hello, glad to see you again. It's good to see you online once more.